PostgreSQL中CTE与历史表计算工作日间隔结果不一致问题

我需要计算两个日期之间包含/不包含周六日的时间间隔，在test_c表中用CTE编写的查询能得到正确结果，但处理存储毫秒级Unix时间戳的tbl_history表时，针对Fulfill阶段计算的时间间隔出现了负数，结果不符合预期。

相关表结构 #

test_c表（正常工作的测试表） #

CREATE TABLE test_c (
    id INT,
    stage VARCHAR(20),
    start_time DATETIME,
    end_time DATETIME
);

tbl_history表（出现异常的表） #

CREATE TABLE tbl_history (
    order_id VARCHAR(50),
    stage VARCHAR(20),
    event_time BIGINT -- 存储毫秒级Unix时间戳
);

正常工作的CTE查询（test_c表） #

WITH stage_intervals AS (
    SELECT
        id,
        stage,
        start_time,
        end_time,
        DATEDIFF(DAY, start_time, end_time) AS total_days,
        -- 计算工作日天数（排除周六日）
        (DATEDIFF(DAY, start_time, end_time) + 1)
        - (DATEDIFF(WEEK, start_time, end_time) * 2)
        - CASE WHEN DATEPART(WEEKDAY, start_time) = 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(WEEKDAY, end_time) = 7 THEN 1 ELSE 0 END AS work_days
    FROM test_c
)
SELECT * FROM stage_intervals;

出现异常的tbl_history查询（Fulfill阶段） #

WITH order_stages AS (
    SELECT
        order_id,
        stage,
        -- 转换毫秒时间戳为datetime
        DATEADD(MILLISECOND, event_time % 1000, DATEADD(SECOND, event_time / 1000, '1970-01-01')) AS event_datetime,
        -- 按订单和阶段排序
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY event_time) AS rn
    FROM tbl_history
),
fulfill_intervals AS (
    SELECT
        os1.order_id,
        os1.event_datetime AS fulfill_start,
        os2.event_datetime AS fulfill_end,
        -- 计算时间间隔（出现负数）
        DATEDIFF(DAY, os1.event_datetime, os2.event_datetime) AS total_days,
        -- 计算工作日间隔
        (DATEDIFF(DAY, os1.event_datetime, os2.event_datetime) + 1)
        - (DATEDIFF(WEEK, os1.event_datetime, os2.event_datetime) * 2)
        - CASE WHEN DATEPART(WEEKDAY, os1.event_datetime) = 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(WEEKDAY, os2.event_datetime) = 7 THEN 1 ELSE 0 END AS work_days
    FROM order_stages os1
    JOIN order_stages os2
        ON os1.order_id = os2.order_id
        AND os1.stage = 'Fulfill'
        AND os2.stage = 'Fulfill'
        AND os1.rn = os2.rn - 1
)
SELECT * FROM fulfill_intervals;

异常输出示例 #

排查原因及修复建议 #

1. 时间戳转换或排序逻辑错误 #

• 毫秒级时间戳转换为datetime时，若event_time存储值异常（如未来时间、无效值），会导致event_datetime顺序颠倒。
• 检查ROW_NUMBER()的排序稳定性：如果event_time存在重复值，排序结果不可控，导致os1.rn = os2.rn -1关联时，开始时间晚于结束时间。
• 临时验证SQL：单独查询异常订单的Fulfill阶段事件，确认时间顺序：

SELECT order_id, stage, event_time, 
       DATEADD(MILLISECOND, event_time % 1000, DATEADD(SECOND, event_time / 1000, '1970-01-01')) AS event_datetime
FROM tbl_history
WHERE order_id = 'OD123' AND stage = 'Fulfill'
ORDER BY event_time;

2. 业务事件的顺序异常 #

• tbl_history中同一订单的Fulfill阶段可能存在反向触发（先记录结束事件，再记录开始事件），直接导致关联后的时间区间颠倒。
• 修复方式：在关联时增加时间顺序校验，或者在业务层面确保事件触发顺序正确。

3. 时区转换遗漏 #

• 毫秒时间戳默认对应UTC时间，若转换为本地时间时未做时区转换，会导致时间偏移，极端情况下跨天甚至顺序颠倒。
• 示例修复（以SQL Server为例）：

DATEADD(MILLISECOND, event_time % 1000, DATEADD(SECOND, event_time / 1000, '1970-01-01')) 
AT TIME ZONE 'UTC' AT TIME ZONE 'China Standard Time' AS event_datetime

4. 工作日计算的边界处理缺失 #

• 当start_time晚于end_time时，原计算逻辑会直接返回负数，需增加顺序判断：

CASE WHEN os1.event_datetime > os2.event_datetime 
     THEN -((DATEDIFF(DAY, os2.event_datetime, os1.event_datetime) + 1)
        - (DATEDIFF(WEEK, os2.event_datetime, os1.event_datetime) * 2)
        - CASE WHEN DATEPART(WEEKDAY, os2.event_datetime) = 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(WEEKDAY, os1.event_datetime) = 7 THEN 1 ELSE 0 END)
     ELSE (DATEDIFF(DAY, os1.event_datetime, os2.event_datetime) + 1)
        - (DATEDIFF(WEEK, os1.event_datetime, os2.event_datetime) * 2)
        - CASE WHEN DATEPART(WEEKDAY, os1.event_datetime) = 1 THEN 1 ELSE 0 END
        - CASE WHEN DATEPART(WEEKDAY, os2.event_datetime) = 7 THEN 1 ELSE 0 END
END AS work_days

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