MySQL分组计算各分类行程耗时遇NULL问题求助

行程样本数据 #

问题说明 #

目标：计算各分类的行程总耗时，例如分类A的预期耗时为02:18:02。

已执行操作：

1. 将CSV时间格式转为YYYY/MM/DD HH:MM:SS，移除表头后导入MySQL；
2. 创建表：

CREATE TABLE trip ( start TIMESTAMP, end TIMESTAMP, category VARCHAR(6) );

3. 执行求和代码：

SELECT category, SUM(TIMEDIFF(end, start)) as length FROM trip GROUP BY CATEGORY;

得到结果：A=34900、B=38000；
4. 尝试转换为时间格式：

SELECT category, Convert(SUM(TIMEDIFF(end, start)), Time) as length FROM trip GROUP BY category;

此时分类A结果为03:49:00符合预期，但分类B结果为NULL，而非预期的03:08:00。

出错原因 #

直接对TIMEDIFF返回的TIME类型值求和后，用Convert(..., Time)转换的逻辑不符合预期：

• TIMEDIFF(end, start)返回TIME类型，SUM()会将其转换为对应的秒数（BIGINT类型）；
• Convert(数值, Time)并非将数值视为秒数转换，而是按照MySQL内部的TIME编码规则处理，导致部分结果出现NULL。

正确实现方法 #

先将每个行程的时间间隔转为秒数，求和后再转换为标准时分秒格式，使用TIME_TO_SEC()和SEC_TO_TIME()函数：

SELECT 
    category,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end, start)))) AS length
FROM trip 
GROUP BY category;

该方法的逻辑是：

1. TIMEDIFF(end, start)获取单条行程的时间间隔；
2. TIME_TO_SEC()将时间间隔转为秒数；
3. SUM()对秒数求和得到总耗时秒数；
4. SEC_TO_TIME()将总秒数转回HH:MM:SS格式的时间字符串。

内容的提问来源于stack exchange，提问作者Maysara