MySQL按月份分组统计班次总时长的查询实现求助

嗨，我明白你遇到的问题了——直接用SUM(TIMEDIFF(End, Start))分组统计确实会出问题，因为TIMEDIFF()返回的是时间类型，MySQL没法正确对时间类型做累加计算，结果自然不对。咱们换个思路，先把时长转成数值型的秒数，求和后再转成时分格式就没问题了。

核心思路 #

• 用TIMESTAMPDIFF(SECOND, Start, End)计算每个班次的总秒数（这个函数直接返回两个时间戳的差值，单位是秒，适合做数值求和）
• 用DATE_FORMAT(Start, '%m')提取班次开始时间的月份作为分组依据
• 最后用SEC_TO_TIME()把总秒数转成HH:MM的时长格式

完整SQL语句 #

假设你的数据表名叫shift_records，替换成你实际的表名就行：

SELECT
    DATE_FORMAT(Start, '%m') AS Month,
    -- 如果想要不带前导零的格式（比如2:15而不是02:15），可以用DATE_FORMAT处理
    DATE_FORMAT(SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, Start, End))), '%k:%i') AS Hours
    -- 若接受带前导零的格式，直接用下面这行即可
    -- SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, Start, End))) AS Hours
FROM
    shift_records
GROUP BY
    DATE_FORMAT(Start, '%m')
ORDER BY
    Month;

针对你的示例数据验证 #

把示例数据代入后：

• 10月的两个班次总秒数是(45*60)+(60*60)=8100秒，用DATE_FORMAT处理后会得到2:15，和你期望的输出完全一致
• 11月的班次总秒数是3600秒，转成1:00

额外补充：处理跨月班次 #

如果你的数据里存在跨月的班次（比如从10月31日23:00到11月1日01:00），上面的方法会把整个时长算到10月，这时候需要拆分记录到对应的月份。可以用递归CTE来实现：

WITH RECURSIVE split_shifts AS (
    SELECT
        ID,
        Start,
        LEAST(End, LAST_DAY(Start) + INTERVAL 1 DAY - INTERVAL 1 SECOND) AS period_end,
        End AS original_end
    FROM shift_records
    UNION ALL
    SELECT
        ID,
        period_end + INTERVAL 1 SECOND,
        LEAST(original_end, LAST_DAY(period_end + INTERVAL 1 SECOND) + INTERVAL 1 DAY - INTERVAL 1 SECOND),
        original_end
    FROM split_shifts
    WHERE period_end < original_end
)
SELECT
    DATE_FORMAT(Start, '%m') AS Month,
    DATE_FORMAT(SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, Start, period_end))), '%k:%i') AS Hours
FROM split_shifts
GROUP BY DATE_FORMAT(Start, '%m')
ORDER BY Month;

这个递归查询会把跨月的班次拆分成每个月内的部分，再分别统计时长，结果更准确。

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