PostgreSQL：如何按班次统计时间戳记录的各时段工作时长

可以通过计算工作时间段与各班次时间范围的交集时长来实现，避免繁琐的分钟数转换逻辑。核心是利用GREATEST和LEAST函数快速定位两个时间段的重叠区间，再计算时长。

班次时间区间定义 #

先明确固定班次的每日时间范围：

• Morning：当日06:00:00 ~ 14:00:00
• Afternoon：当日14:00:00 ~ 22:00:00
• Night：当日22:00:00 ~ 次日06:00:00（需拆分跨天场景）

完整SQL查询语句 #

假设你的表名为work_records，替换为实际表名即可：

SELECT
    id,
    data,
    start_at,
    end_at,
    contractor,
    -- 计算Morning班次时长（保留1位小数，可按需调整）
    ROUND(
        EXTRACT(EPOCH FROM 
            GREATEST(LEAST(end_at, DATE(start_at) + INTERVAL '14 hours'), start_at) - 
            GREATEST(start_at, DATE(start_at) + INTERVAL '6 hours')
        ) / 3600, 1
    ) AS morning,
    -- 计算Afternoon班次时长
    ROUND(
        EXTRACT(EPOCH FROM 
            GREATEST(LEAST(end_at, DATE(start_at) + INTERVAL '22 hours'), start_at) - 
            GREATEST(start_at, DATE(start_at) + INTERVAL '14 hours')
        ) / 3600, 1
    ) AS afternoon,
    -- 计算Night班次时长（处理跨天情况）
    ROUND(
        -- 当日22:00至结束的时长
        (EXTRACT(EPOCH FROM GREATEST(LEAST(end_at, DATE(start_at) + INTERVAL '1 day'), start_at) - GREATEST(start_at, DATE(start_at) + INTERVAL '22 hours')) / 3600) +
        -- 次日00:00至06:00的时长（仅当结束时间跨天）
        CASE 
            WHEN end_at > DATE(start_at) + INTERVAL '1 day' THEN
                EXTRACT(EPOCH FROM GREATEST(LEAST(end_at, DATE(start_at) + INTERVAL '1 day 6 hours'), DATE(start_at) + INTERVAL '1 day') - DATE(start_at) + INTERVAL '1 day') / 3600
            ELSE 0 
        END,
    1) AS night
FROM work_records;

关键逻辑说明 #

1. Morning/Afternoon班次计算：

   • 先基于start_at的日期，生成当日班次的起止时间戳（比如DATE(start_at) + INTERVAL '6 hours'就是当天6点）
   • 用GREATEST(start_at, 班次开始时间)获取交集的实际起始点，LEAST(end_at, 班次结束时间)获取交集的实际结束点
   • 若起始点≤结束点，通过EXTRACT(EPOCH FROM ...)计算时间差的秒数，除以3600转为小时，最后用ROUND控制小数位数

2. Night班次计算：

   • 拆分两部分计算：当日22点到工作结束的时长，以及次日0点到6点的时长（仅当工作结束时间跨到次日时生效）
   • 跨天部分通过判断end_at是否超过次日0点触发计算，取次日0点到工作结束时间、6点的最小值的交集时长

适配示例数据的结果 #

以你提供的前两条记录为例：

• id=1：Morning为6.0小时，Afternoon为4.5小时，Night为0.0小时
• id=2：Morning为0.0小时，Afternoon为5.0小时，Night为1.5小时

如果需要和你的示例输出一样取整，只需将ROUND(..., 1)改为ROUND(..., 0)或FLOOR(...)即可。

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