php - Mysql timediff返回两个不同的值

Question

我有一个查询用于测试时间。

我得到了两个不同的时间，这让我怀疑我的数据的正确性。

下面是两个例子:

SELECT 
    users.fname as counselor,
    count(session.anum) as students,
    SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(starttime, signintime)))) AS 'AVG Wait Time',
    SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(finishtime, starttime)))) AS 'AVG Session Length'
FROM session
    LEFT JOIN support 
        ON support.session_id = session.session_id
    LEFT JOIN users 
        ON users.username = support.counselor
WHERE session.status = 3 
GROUP BY fname;

返回:

00:01:17 and 00:00:05

下一个查询是:

SELECT 
    users.fname as counselor,
    count(session.anum) as students,
    SEC_TO_TIME(AVG(TIMEDIFF(starttime, signintime))) AS 'AVG Wait Time',
    SEC_TO_TIME(AVG(TIMEDIFF(finishtime, starttime))) AS 'AVG Session Length'
FROM session
    LEFT JOIN support 
        ON support.session_id = session.session_id
    LEFT JOIN users 
        ON users.username = support.counselor
WHERE session.status = 3 
GROUP BY fname;

00:01:37 and 00:00:05

为什么会发生这种情况，我应该信任哪个结果集？如果有人能消除我的困惑，我会很高兴。

Answer 1

TIMEDIFF() 返回一个字符串，例如

TIMEDIFF('13:07:33', '9:5:37') -> 04:01:56

如果你直接求平均值，mysql 将尝试将 04:01:56 转换为 int 并以 avg(04) 结束。

这就是为什么您必须 time_to_sec，它以简单的 int 形式返回该时间值:14516，可以直接求平均值。