GROUP BY函数返回

Question

类别

开始		结束
2022:10:14 17:13:00	2022:10:1417:19:00	A
2022:10:01 16:29:00	2022:10:01 16:49:00	B
2022:10:19 18:55:00
2022:10:31 07:52:00	2022:10:31 07:58:00	A
2022:10:13 18:41:00	2022:10:13 19:26:00	B

您可以按如下方式执行：

这对于超越MySQL的TIME值限制838:59:59 非常有用

SELECT category, 
CONCAT(FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600),":",FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60),":",(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)%60) as `length`
FROM trip
GROUP BY category;

这是为了获得像00:20:00这样的时间，而不是0:20:0

SELECT category, 
CONCAT(
if(FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600) > 10, FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600), CONCAT('0',FLOOR(SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))/3600)) ) ,
":",
if(FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60) > 10, FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60), CONCAT('0', FLOOR((SUM(TIMESTAMPDIFF(SECOND, `start`, `end`))%3600)/60) ) ),
":",
if( (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60 > 10, (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60, concat('0', (SUM(TIMESTAMPDIFF(SECOND, `start`, `end`) )%3600)%60))
) as `length`
FROM trip
GROUP BY category;

您可以计算每个单独行程的长度(以秒为单位)，得到每个类别的长度总和，然后将秒转换为时间：

SELECT category, SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, `end`, `start`))) as `length`
FROM trip
GROUP BY category;

如果SUM()超过TIME数据类型的限制(838:59:59)，则将返回此MAXVALUE。

---

对于超过TIME值限制的值，使用

SELECT category,
CONCAT_WS(':',
secs DIV (60 * 60),
LPAD(secs DIV 60 MOD 60, 2, 0),
LPAD(secs MOD 60, 2, 0)) AS `length`
FROM (
SELECT category, SUM(TIMESTAMPDIFF(SECOND, `end`, `start`)) AS secs
FROM trip
GROUP BY category
) subquery
;