PostgreSQL中如何正确计算工作日与全天候时间间隔？

问题原因 #

你计算bus_interval时出现多1天的错误，根源在于工作日计数和间隔计算的逻辑错误：

• 你的bus_days通过generate_series生成从起始日期到结束日期的所有日期并统计工作日，比如FirstLineSupport的起始日2023-05-23（周二）到结束日2023-05-26（周五），会统计到4个工作日（23、24、25、26号）。
• 后续用bus_days -1生成天数间隔，再加上时间差部分，相当于把首尾两个部分天数的日期算成了完整的1天额外天数，导致最终结果多了1天。

正确计算方法 #

方法1：通过总间隔减去周末时长（简洁高效） #

核心逻辑是先算出全天候总间隔，再减去时间段内包含的周六、周日的总时长（每天24小时），得到工作日间隔：

SELECT
    t.entity_id,
    t.type_de_demande,
    t.phase,
    CASE WHEN ts.end_ts IS NULL THEN NULL
         ELSE (ts.end_ts - ts.start_ts) 
              - (COUNT(*) FILTER (WHERE EXTRACT(ISODOW FROM gs.day) IN (6,7)) * INTERVAL '1 day')
    END AS bus_interval,
    ts.end_ts - ts.start_ts AS interval_24x7,
    ts.start_ts,
    ts.end_ts,
    t.time,
    t.next_time
FROM requests AS t
LEFT JOIN LATERAL (
    SELECT 
        to_timestamp(t.time / 1000)::timestamp AS start_ts,
        to_timestamp(t.next_time / 1000)::timestamp AS end_ts
) AS ts ON true
LEFT JOIN LATERAL generate_series(
    ts.start_ts::DATE, 
    ts.end_ts::DATE, 
    INTERVAL '1 day'
) AS gs(day) ON true
GROUP BY t.entity_id, t.type_de_demande, t.phase, ts.start_ts, ts.end_ts, t.time, t.next_time
ORDER BY t.phase;

方法2：拆分首尾部分天数+中间完整工作日（精确可控） #

如果需要更精细的时间拆分计算，可以单独处理首尾工作日的部分时长，加上中间完整工作日的总时长：

SELECT
    t.entity_id,
    t.type_de_demande,
    t.phase,
    CASE 
        WHEN ts.end_ts IS NULL THEN NULL
        ELSE (
            -- 起始日的有效时长（仅当起始日是工作日）
            CASE WHEN EXTRACT(ISODOW FROM ts.start_ts) < 6 
                 THEN LEAST(ts.start_ts::DATE + INTERVAL '1 day', ts.end_ts) - ts.start_ts
                 ELSE INTERVAL '0' END
            +
            -- 结束日的有效时长（仅当结束日是工作日）
            CASE WHEN EXTRACT(ISODOW FROM ts.end_ts) < 6
                 THEN ts.end_ts - GREATEST(ts.end_ts::DATE, ts.start_ts)
                 ELSE INTERVAL '0' END
            +
            -- 中间完整工作日的总时长
            (COUNT(*) FILTER (WHERE gs.day BETWEEN ts.start_ts::DATE + INTERVAL '1 day' AND ts.end_ts::DATE - INTERVAL '1 day') * INTERVAL '1 day')
        )
    END AS bus_interval,
    ts.end_ts - ts.start_ts AS interval_24x7,
    ts.start_ts,
    ts.end_ts,
    t.time,
    t.next_time
FROM requests AS t
LEFT JOIN LATERAL (
    SELECT 
        to_timestamp(t.time / 1000)::timestamp AS start_ts,
        to_timestamp(t.next_time / 1000)::timestamp AS end_ts
) AS ts ON true
LEFT JOIN LATERAL generate_series(
    ts.start_ts::DATE, 
    ts.end_ts::DATE, 
    INTERVAL '1 day'
) AS gs(day) ON EXTRACT(ISODOW FROM gs.day) < 6
GROUP BY t.entity_id, t.type_de_demande, t.phase, ts.start_ts, ts.end_ts, t.time, t.next_time
ORDER BY t.phase;

两种方法都能得到正确的FirstLineSupport工作日间隔：{"days":2,"hours":22,"minutes":26,"seconds":43}，与全天候间隔的日期部分一致。

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