mysql - 查询中产生的多次求和 (timediff)

Question

我有两个字段:

• 初始(时间戳)
• 最终(时间戳)

我的查询是:

SELECT TIMEDIFF(Final, Initial) 
    AS 'Worked Hours' 
    FROM `db_foo`
    WHERE matriculation='X' AND date='2017-yy-yy'

结果会是这样的

Worked Hours    
03:34:00
02:34:00
01:00:00
[...]

是否可以将这些多个时间戳进一步相加，从而得到总工作时间？

示例数据集(以 csv 格式导出):

DATE --- ID --- INITIAL --- FINAL --- MATRICULATION

2017-09-14,"29","2017-09-14 11:00:00","2017-09-14 14:34:00","4"
2017-09-14,"30","2017-09-14 17:00:00","2017-09-14 19:34:00","4"
2017-09-14,"31","2017-09-14 21:00:00","2017-09-14 22:00:00","4"

期望的输出(它是工作时间的总和):

Worked Hours
07:08:00

提前致谢

Answer 1

TIME 类型的最大值为 838:59:59。如果您认为总和可能超过 838 小时，则对 TIME 表达式求和是不安全的。我建议将时差转换为分钟，并将总小时数显示为十进制数，而不是时间:

SELECT
  ROUND(SUM(TIMESTAMPDIFF(MINUTE, Initial, Final) / 60.0), 1) AS "Worked Hours"
FROM `db_foo`
WHERE matriculation='X' AND date='2017-yy-yy';

哪个会返回

Worked Hours
7.1