mysql - 从两个数据库表中选择平均响应时间

Question

我在 MySQL 中有两个表 - tickets 和 ticket_updates

具有以下结构:

门票

• 序列
• 票号
• 日期时间

ticket_updates

• 序列
• 票号
• 日期时间

关系是:tickets.ticketnumber = Ticket_updates.ticketnumber

我想根据行插入 ticket_updates 的时间找出某一天的平均响应时间

我尝试了这个查询:

SELECT avg(response_seconds) as s FROM 
    (SELECT time_to_sec(timediff(min(u.datetime), u.datetime)) AS response_seconds 
    FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber) 
    AS r 

但每次都会返回0.000

Answer 1

我认为问题在于:

(SELECT time_to_sec(timediff(min(u.datetime), u.datetime)) AS response_seconds

在我的控制台上返回 0。尝试使用:

( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) ) as response_seconds;

喜欢

SELECT avg(response_seconds) as s FROM 
    ( select time_to_sec( timediff( from_unixtime( floor( UNIX_TIMESTAMP(u.datetime)/60 )*60 ), u.datetime) ) ) as response_seconds 
    FROM tickets t JOIN ticket_updates u ON t.ticketnumber = u.ticketnumber 
    WHERE u.type = 'update' and t.customer = 'Y' and DATE(u.datetime) = '2016-04-18' 
    GROUP BY t.ticketnumber) 
    AS r