PostgreSQL计算两个时间戳间工作日时长（排除周末小时数）

以下是针对需求的解决方案，通过CTE生成日期序列并逐天计算有效时长，最终求和得到工作日总小时数：

核心查询代码 #

WITH date_range AS (
    -- 生成覆盖起始到结束日期的每日0点时间序列
    SELECT generate_series(
        DATE_TRUNC('day', created_on),
        DATE_TRUNC('day', end_time),
        INTERVAL '1 day'
    ) AS day_start
),
daily_hours AS (
    SELECT
        day_start,
        -- 确定当天的有效开始时间：起始日期取实际时间，其余取当天0点
        GREATEST(day_start, created_on) AS period_start,
        -- 确定当天的有效结束时间：结束日期取实际时间，其余取次日0点（即当天24点）
        LEAST(day_start + INTERVAL '1 day', end_time) AS period_end,
        -- 判断是否为工作日（周一至周五）
        EXTRACT(DOW FROM day_start) BETWEEN 1 AND 5 AS is_weekday
    FROM date_range
)
SELECT
    SUM(
        CASE
            WHEN is_weekday THEN EXTRACT(EPOCH FROM (period_end - period_start)) / 3600
            ELSE 0
        END
    ) AS working_hours
FROM daily_hours;

代入示例验证 #

将你的示例参数代入查询，可直接得到预期的66小时：

WITH date_range AS (
    SELECT generate_series(
        DATE_TRUNC('day', '2023-04-27 14:00:00'::TIMESTAMP),
        DATE_TRUNC('day', '2023-05-02 08:00:00'::TIMESTAMP),
        INTERVAL '1 day'
    ) AS day_start
),
daily_hours AS (
    SELECT
        day_start,
        GREATEST(day_start, '2023-04-27 14:00:00'::TIMESTAMP) AS period_start,
        LEAST(day_start + INTERVAL '1 day', '2023-05-02 08:00:00'::TIMESTAMP) AS period_end,
        EXTRACT(DOW FROM day_start) BETWEEN 1 AND 5 AS is_weekday
    FROM date_range
)
SELECT
    SUM(
        CASE
            WHEN is_weekday THEN EXTRACT(EPOCH FROM (period_end - period_start)) / 3600
            ELSE 0
        END
    ) AS working_hours
FROM daily_hours;

逻辑说明 #

1. date_range：生成从起始日期零点到结束日期零点的所有日期，确保不遗漏任何涉及的天数。
2. daily_hours：
   • 对每一天，分别计算当天的有效计时区间：起始日取实际开始时间，结束日取实际结束时间，中间完整日期取全天。
   • 通过EXTRACT(DOW)判断工作日（DOW返回0为周日、6为周六，1-5对应周一至周五）。
3. 求和计算：将每个工作日的有效时长（秒转小时）累加，非工作日计0，最终得到总工作日小时数。

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