计算两个Datetime时间差并排除周末，解决起始日为周末时多行结果问题

| time_diff | #

| 8 days 4 hours 37 minutes 48.000000 seconds | #

#### 异常情况（起始为周日）
```sql
WITH test AS (
    SELECT EXTRACT(DAY FROM diff) || ' days '
    || EXTRACT(HOUR FROM diff) || ' hours '
    || EXTRACT(MINUTE FROM diff) || ' minutes '
    || EXTRACT(SECOND FROM diff) || ' seconds ' AS time_diff
FROM (
    SELECT TIMESTAMP '2023-01-10 15:12:24' - TIMESTAMP '2023-01-01 10:34:36' AS diff
) AS diff_query
JOIN (
    SELECT generate_series(
             timestamp '2023-01-01',
             timestamp '2023-01-10',
             interval '3 day'
           ) AS the_day
    ) AS dates
    ON dates.the_day BETWEEN TIMESTAMP '2023-01-01 10:34:36' AND TIMESTAMP '2023-01-10 15:12:24'
WHERE EXTRACT('ISODOW' FROM dates.the_day) < 6
)

SELECT * FROM test

输出：

-----------------------------------------------
| time_diff                                   |
-----------------------------------------------
| 9 days 4 hours 37 minutes 48.000000 seconds |
| 9 days 4 hours 37 minutes 48.000000 seconds |
-----------------------------------------------

问题根源 #

原代码逻辑存在两处核心问题：

1. generate_series步长错误：用interval '3 day'会跳过大部分日期，无法遍历所有需要检查的日期
2. JOIN导致重复行：diff_query只有一行总时间差，JOIN符合条件的工作日日期行后，结果行数等于工作日日期的数量，而非计算实际排除周末的时间差
3. 未正确扣除周末时长：原输出的时间差是两个时间点的总时长，并没有减去周末的时间，结果本身不符合需求

解决方案 #

方案1：完全修正逻辑（正确计算排除周末的时间差+单行结果）

以下SQL既保证返回单行结果，又精准计算排除周六周日的有效时间差：

WITH date_range AS (
    -- 生成时间范围内的每一天（步长1天，按天截断）
    SELECT generate_series(
             DATE_TRUNC('day', TIMESTAMP '2023-01-01 10:34:36'),
             DATE_TRUNC('day', TIMESTAMP '2023-01-10 15:12:24'),
             INTERVAL '1 day'
           ) AS day_start
),
weekdays AS (
    -- 筛选工作日：ISODOW 1-5对应周一到周五
    SELECT day_start
    FROM date_range
    WHERE EXTRACT(ISODOW FROM day_start) < 6
),
work_time AS (
    -- 计算每个工作日的有效时长
    SELECT
        CASE
            -- 起始当天：计算从开始时间到当天结束的时长
            WHEN day_start = DATE_TRUNC('day', TIMESTAMP '2023-01-01 10:34:36') THEN
                LEAST(day_start + INTERVAL '1 day', TIMESTAMP '2023-01-10 15:12:24') - TIMESTAMP '2023-01-01 10:34:36'
            -- 结束当天：计算从当天开始到结束时间的时长
            WHEN day_start = DATE_TRUNC('day', TIMESTAMP '2023-01-10 15:12:24') THEN
                TIMESTAMP '2023-01-10 15:12:24' - day_start
            -- 中间工作日：按全天24小时计算
            ELSE INTERVAL '1 day'
        END AS duration
    FROM weekdays
)
-- 汇总有效时长并格式化为指定字符串
SELECT
    EXTRACT(DAY FROM total_diff) || ' days ' ||
    EXTRACT(HOUR FROM total_diff) || ' hours ' ||
    EXTRACT(MINUTE FROM total_diff) || ' minutes ' ||
    EXTRACT(SECOND FROM total_diff) || ' seconds ' AS time_diff
FROM (
    SELECT SUM(duration) AS total_diff FROM work_time
) AS total;

方案2：临时去重（仅修复行数，不修正时间差计算逻辑）

如果只是想快速解决重复行问题（不修正原代码未扣除周末时长的错误），可以在最终查询时添加DISTINCT关键字：

WITH test AS (
    SELECT EXTRACT(DAY FROM diff) || ' days '
    || EXTRACT(HOUR FROM diff) || ' hours '
    || EXTRACT(MINUTE FROM diff) || ' minutes '
    || EXTRACT(SECOND FROM diff) || ' seconds ' AS time_diff
FROM (
    SELECT TIMESTAMP '2023-01-10 15:12:24' - TIMESTAMP '2023-01-01 10:34:36' AS diff
) AS diff_query
JOIN (
    SELECT generate_series(
             timestamp '2023-01-01',
             timestamp '2023-01-10',
             interval '3 day'
           ) AS the_day
    ) AS dates
    ON dates.the_day BETWEEN TIMESTAMP '2023-01-01 10:34:36' AND TIMESTAMP '2023-01-10 15:12:24'
WHERE EXTRACT('ISODOW' FROM dates.the_day) < 6
)

SELECT DISTINCT * FROM test;

注意：此方法仅去重，结果仍然是两个时间点的总时长，未排除周末时间，仅适用于临时应急场景。

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