如何在SQL中计算时间差总和对应的天/时/分/秒格式平均值

实现方案（MySQL环境） #

原有使用TIMEDIFF求和的逻辑存在最大838小时的截断限制，总时间差超过该值时会出现精度丢失，建议先将每行时间差转换为秒数计算平均值，再格式化转为天、时、分、秒格式，具体实现如下：

SELECT 
    CONCAT(
        FLOOR(avg_seconds / 86400), '天 ',
        SEC_TO_TIME(avg_seconds % 86400)
    ) AS avg_time_diff
FROM (
    SELECT 
        SUM(TIMESTAMPDIFF(SECOND, `start_time`, `end_time`)) / COUNT(*) AS avg_seconds
    FROM my_table
) AS tmp;

逻辑说明

• 内层子查询使用TIMESTAMPDIFF计算每行时间差的秒数，求和后除以总行数得到平均秒数，完全避免时间差截断问题
• 86400为一天的总秒数，FLOOR(avg_seconds / 86400)即可得到完整的天数
• SEC_TO_TIME(avg_seconds % 86400)将扣除整天后剩余的秒数转换为小时:分钟:秒格式
• 最终用CONCAT拼接为需求的格式，示例输出：2天 03:25:12

可选优化：不满1天隐藏天数

如果平均时间差不足1天时不需要显示天数，可以加判断逻辑实现：

SELECT 
    IF(
        avg_seconds >= 86400,
        CONCAT(FLOOR(avg_seconds / 86400), '天 ', SEC_TO_TIME(avg_seconds % 86400)),
        SEC_TO_TIME(avg_seconds)
    ) AS avg_time_diff
FROM (
    SELECT 
        SUM(TIMESTAMPDIFF(SECOND, `start_time`, `end_time`)) / COUNT(*) AS avg_seconds
    FROM my_table
) AS tmp;

注意事项

如果存在start_time或end_time为空的无效行，可以在内层查询的FROM my_table后添加过滤条件：
WHERE start_time IS NOT NULL AND end_time IS NOT NULL

内容的提问来源于stack exchange，提问作者Marcello D'Ambrosio