MySQL按15分钟时间区间分组统计员工队列状态时长问题

这个场景我碰到过很多次——你原来的写法只把时长算进了登录的第一个15分钟区间，自然没法处理跨区间的情况。要实现你想要的拆分统计，核心思路是先生成覆盖所有考勤时间范围的15分钟时间区间序列，再让每条考勤记录和这些区间做关联，计算每个区间内的实际停留时长。

下面是针对MySQL的完整解决方案（考虑到你用了convert_tz，应该是MySQL环境）：

完整SQL代码 #

WITH RECURSIVE time_intervals AS (
    -- 生成第一个区间：取考勤表最早StartTime，向下对齐到最近的15分钟整点
    SELECT 
        DATE_FORMAT(
            DATE_SUB(
                convert_tz(min(StartTime), 'UTC', 'America/Denver'),
                INTERVAL MINUTE(convert_tz(min(StartTime), 'UTC', 'America/Denver')) % 15 MINUTE
            ),
            '%Y-%m-%d %H:%i:00'
        ) AS interval_start,
        DATE_FORMAT(
            DATE_ADD(
                DATE_SUB(
                    convert_tz(min(StartTime), 'UTC', 'America/Denver'),
                    INTERVAL MINUTE(convert_tz(min(StartTime), 'UTC', 'America/Denver')) % 15 MINUTE
                ),
                INTERVAL 15 MINUTE
            ),
            '%Y-%m-%d %H:%i:00'
        ) AS interval_end
    FROM attendance_table
    UNION ALL
    -- 递归生成后续的15分钟区间
    SELECT 
        interval_end,
        DATE_ADD(interval_end, INTERVAL 15 MINUTE)
    FROM time_intervals
    -- 终止条件：区间结束时间超过考勤表最晚EndTime的对齐时间
    WHERE interval_end < (
        SELECT DATE_ADD(
            convert_tz(max(EndTime), 'UTC', 'America/Denver'),
            INTERVAL (60 - MINUTE(convert_tz(max(EndTime), 'UTC', 'America/Denver')) % 15) % 15 MINUTE
        ) FROM attendance_table
    )
),
-- 预转换考勤时间到丹佛时区，避免重复计算
attendance_local AS (
    SELECT 
        Employee_ID,
        Queue,
        convert_tz(StartTime, 'UTC', 'America/Denver') AS local_start,
        convert_tz(EndTime, 'UTC', 'America/Denver') AS local_end
    FROM attendance_table
)
-- 关联区间与考勤记录，计算每个区间的有效时长
SELECT 
    al.Employee_ID,
    DATE_FORMAT(ti.interval_start, '%H:%i') AS `Interval`,
    al.Queue,
    -- 计算重叠时长：取两个时间段的交集，转换为时分秒格式
    SEC_TO_TIME(
        TIMESTAMPDIFF(SECOND, 
            GREATEST(ti.interval_start, al.local_start),
            LEAST(ti.interval_end, al.local_end)
        )
    ) AS QueueTime
FROM time_intervals ti
JOIN attendance_local al 
    -- 只关联有时间重叠的记录
    ON al.local_start < ti.interval_end 
    AND al.local_end > ti.interval_start
ORDER BY al.Employee_ID, ti.interval_start, al.Queue;

代码解释 #

1. time_intervals 递归CTE：

   • 自动生成所有覆盖考勤数据时间范围的15分钟区间，从最早的考勤记录时间向下对齐到15分钟整点开始，到最晚的考勤记录时间向上对齐到15分钟整点结束。
   • 递归逻辑会不断生成下一个15分钟区间，直到覆盖所有需要的时间段。

2. attendance_local CTE：

   • 提前把所有UTC时间转换为丹佛时区，避免在后续关联中重复执行时区转换，提升效率。

3. 关联查询与时长计算：

   • 通过重叠条件al.local_start < ti.interval_end AND al.local_end > ti.interval_start，筛选出所有和当前考勤记录有交集的时间区间。
   • 用GREATEST和LEAST计算两个时间段的交集（也就是员工在这个15分钟区间内的实际停留时间），再通过TIMESTAMPDIFF和SEC_TO_TIME转换为易读的时分秒格式。

适配旧版本MySQL（8.0以下） #

如果你的MySQL版本不支持递归CTE，可以用数字表生成区间：

-- 先创建一个数字表（比如包含0到1000的数字，足够覆盖大部分场景）
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),...,(1000);

-- 生成时间区间
SELECT 
    DATE_FORMAT(
        DATE_ADD(
            DATE_SUB(
                convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver'),
                INTERVAL MINUTE(convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver')) %15 MINUTE
            ),
            INTERVAL n*15 MINUTE
        ),
        '%Y-%m-%d %H:%i:00'
    ) AS interval_start,
    DATE_FORMAT(
        DATE_ADD(
            DATE_SUB(
                convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver'),
                INTERVAL MINUTE(convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver')) %15 MINUTE
            ),
            INTERVAL (n+1)*15 MINUTE
        ),
        '%Y-%m-%d %H:%i:00'
    ) AS interval_end
FROM numbers
WHERE DATE_ADD(
            DATE_SUB(
                convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver'),
                INTERVAL MINUTE(convert_tz((SELECT min(StartTime) FROM attendance_table), 'UTC', 'America/Denver')) %15 MINUTE
            ),
            INTERVAL n*15 MINUTE
        ) < (
            SELECT DATE_ADD(
                convert_tz(max(EndTime), 'UTC', 'America/Denver'),
                INTERVAL (60 - MINUTE(convert_tz(max(EndTime), 'UTC', 'America/Denver')) %15) %15 MINUTE
            ) FROM attendance_table
        )

之后的关联和时长计算逻辑和上面一致。

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