MySQL 查询返回当前结果的时间总和

Question

所以我的查询是这样的:

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

我的查询返回:

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

是否有一种方法可以修改此查询以仅返回此字段的 SUM，从而仅返回一个字段？

谢谢

Answer 1

您可以使用TIME_TO_SEC()将时间间隔转换为秒数，对其求和，然后使用 SEC_TO_TIME()将结果表示为时间值。

还可以重新排列 TIMEDIFF()表达式简单地将 2 小时添加到 end 的每个值上:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIME(end)) + 2*60*60))
FROM   sworkingtime
WHERE  user = 'magdalena'
   AND type = 'work'
   AND start BETWEEN '2014-03-01' AND '2014-03-28'
   AND (HOUR(end) > 22 OR HOUR(end) < 4)
   AND completed = 1