mysql - 高级MySQL时间总和

Question

我有 MySQL 查询，它工作正常并返回带有时间值的多行。我正在寻找对所有这些时间值求和并将它们作为单行返回的可能性。

所以查询是这样的:

SELECT TIMEDIFF('24:00:00',(TIMEDIFF('22:00:00',TIME(end)))) AS time
FROM sn_workingtime
WHERE user='magdalena'
AND type='work'
AND start >= '2014-03-01' AND start <= '2014-03-31 23:59:59'
AND (HOUR(`end`) > 22 OR HOUR(`end`) < 4)
AND completed=1

此查询返回:

time
02:02:36
03:17:24
03:07:03
02:24:17
03:14:09

我希望此查询返回单行中所有时间值的以下总和，如下所示:

time
14:05:29

MySQL 有没有办法“自动”求和给定的结果集？谢谢!

Answer 1

试试这个我的 friend ，你可以将你的时间转换为秒，然后将所有秒数相加，最后将这个秒转换为时间:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(t.time))) as time FROM 
(SELECT TIMEDIFF('24:00:00',(TIMEDIFF('22:00:00',TIME(end)))) AS time
FROM sn_workingtime
WHERE user='magdalena'
AND type='work'
AND start >= '2014-03-01' AND start <= '2014-03-31 23:59:59'
AND (HOUR(`end`) > 22 OR HOUR(`end`) < 4)
AND completed=1)t;

看看 here