MySQL时间格式转换问题：将聚合后的时分秒格式转为分秒格式

我太懂这种卡壳的感觉了——试了一堆函数，单独用都好好的，一聚合就出问题，简直挠头！让我帮你捋清楚问题出在哪，再给你俩靠谱的解决方案。

首先，咱们得搞明白为啥之前的方法行不通：你尝试先把time_avg用TIME_FORMAT转成%i:%s再计算平均值，这时候踩了个大坑——MySQL把TIME_FORMAT输出的'29:47'这种字符串解析成29小时47分钟了！不是你以为的29分47秒！这就导致time_to_sec计算出来的秒数完全不对，聚合后的结果自然乱七八糟。

而单独执行TIME_FORMAT能得到正确结果，是因为那时候没有聚合，只是直接格式化单个时间值，MySQL不会把它当成小时来解析。

那正确的思路应该是：先计算出所有time_avg的平均总秒数，再把这个总秒数转换成mm:ss[.小数]的格式，而不是依赖sec_to_time（它一定会输出完整的HH:MM:SS格式）。

方案一：纯数学运算拼接格式（灵活可控） #

这个方法直接对平均总秒数做计算，拆分出分钟、秒和小数部分，完全自定义格式：

SELECT 
    location,
    CONCAT(
        -- 计算总分钟数（平均秒数除以60取整）
        FLOOR(AVG(time_to_sec(time_avg)) / 60),
        ':',
        -- 计算剩余秒数，用LPAD保证两位数（比如5秒变成05）
        LPAD(FLOOR(AVG(time_to_sec(time_avg)) % 60), 2, '0'),
        -- 如果有小数秒，保留小数部分；没有的话就不显示
        IF(AVG(time_to_sec(time_avg)) != FLOOR(AVG(time_to_sec(time_avg))),
           CONCAT('.', SUBSTRING_INDEX(AVG(time_to_sec(time_avg)) - FLOOR(AVG(time_to_sec(time_avg))), '.', -1)),
           '')
    ) AS timeAVG
FROM test.test
GROUP BY location;

比如你的示例结果00:26:13.3333，用这个方法会输出26:13.3333，完美符合你的需求。

方案二：借助DATE_FORMAT处理小时转分钟 #

如果你更习惯用日期函数，可以先通过sec_to_time得到完整的时间格式，再把小时转换成分钟加到分钟数里，最后拼接秒部分：

SELECT 
    location,
    CONCAT(
        -- 把小时转成分钟，加到原分钟数上
        DATE_FORMAT(sec_to_time(AVG(time_to_sec(time_avg))), '%i') + 
        DATE_FORMAT(sec_to_time(AVG(time_to_sec(time_avg))), '%H') * 60,
        -- 拼接秒和小数部分（不需要小数的话去掉.%f）
        DATE_FORMAT(sec_to_time(AVG(time_to_sec(time_avg))), ':%s.%f')
    ) AS timeAVG
FROM test.test
GROUP BY location;

这个方法对于习惯日期函数的人更友好，同样能得到mm:ss.xxxx的格式。

最后再划个重点 #

以后处理时间聚合转格式，一定要先聚合计算总秒数，再做格式转换！别搞反顺序，不然很容易踩MySQL解析字符串的坑～

内容的提问来源于stack exchange，提问作者Avalon Parkes-barton