PostgreSQL中如何计算事件在工作时段内的持续时长？

完全可以仅通过PostgreSQL查询实现需求，无需获取原始数据后在后端处理。以下是具体实现方案：

实现思路 #

1. 生成事件时间范围内的所有日期序列，过滤出周一至周五的工作日
2. 为每个工作日生成当天的工作时段起止时间（9:00-18:00，带时区）
3. 计算每个工作日内，事件时间区间与工作时段的重叠时长
4. 将每个事件的所有工作日重叠时长求和，得到最终的工作时段总时长

完整SQL查询 #

WITH event_dates AS (
    -- 生成事件时间范围内的所有日期（按天拆分）
    SELECT 
        id,
        start_timestamp,
        end_timestamp,
        generate_series(
            date_trunc('day', start_timestamp)::timestamptz,
            date_trunc('day', end_timestamp)::timestamptz,
            INTERVAL '1 day'
        ) AS day_start
    FROM my_table
),
work_hours AS (
    -- 计算每个日期对应的工作时段起止
    SELECT
        id,
        start_timestamp,
        end_timestamp,
        day_start + INTERVAL '9 hours' AS work_start,
        day_start + INTERVAL '18 hours' AS work_end,
        day_start
    FROM event_dates
    -- 过滤周末（ISO周规则：周一=1，周日=7）
    WHERE EXTRACT(ISODOW FROM day_start) BETWEEN 1 AND 5
),
overlap_calculation AS (
    -- 计算每个工作日的重叠时长（秒）
    SELECT
        id,
        GREATEST(
            0,
            EXTRACT(EPOCH FROM (
                LEAST(end_timestamp, work_end) - GREATEST(start_timestamp, work_start)
            ))
        ) AS daily_working_seconds
    FROM work_hours
)
-- 汇总每个事件的总工作时段时长
SELECT
    t.id,
    t.start_timestamp,
    t.end_timestamp,
    t.duration,
    COALESCE(SUM(oc.daily_working_seconds), 0) AS working_hours_duration
FROM my_table t
LEFT JOIN overlap_calculation oc ON t.id = oc.id
GROUP BY t.id, t.start_timestamp, t.end_timestamp, t.duration
ORDER BY t.id;

查询结果验证 #

执行上述查询后，将得到与预期完全匹配的结果：

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