PostgreSQL如何实现日期加指定SLA天数并跳过周末计算截止日期

实现方案 #

PostgreSQL 中可以通过两种常用方式实现排除周末的工作日累加计算： #

方案1：日期序列遍历法（逻辑简单易调试，适合小数据量场景）

核心逻辑是从起始日期后一天开始生成连续日期，过滤掉周六周日，取第slaTime个符合要求的日期作为截止日期：

SELECT
    id,
    title,
    created_at,
    slaTime,
    (
        SELECT d::date
        FROM generate_series(
            created_at::date + INTERVAL '1d',
            created_at::date + INTERVAL '1d' * slaTime * 2,
            INTERVAL '1d'
        ) t(d)
        -- ISODOW 规则：1=周一，6=周六，7=周日
        WHERE EXTRACT(ISODOW FROM d) NOT IN (6,7)
        ORDER BY d
        LIMIT 1 OFFSET slaTime - 1
    ) AS deadline
FROM proses;

该方案计算结果完全匹配你的示例：id为1的记录返回2012-11-26，id为2的记录返回2012-11-30。 #

方案2：数学公式计算法（性能更高，适合百万级以上大表场景）

不需要遍历日期，直接通过周数计算需要跳过的周末天数，性能远高于遍历方案：

SELECT
    id,
    title,
    created_at,
    slaTime,
    (
        created_at::date
        + slaTime * INTERVAL '1d'
        -- 计算完整周的周末天数
        + (slaTime + EXTRACT(ISODOW FROM created_at)::int) / 5 * 2 * INTERVAL '1d'
        -- 处理不满一周的剩余天数里包含的周末
        + CASE WHEN EXTRACT(ISODOW FROM created_at)::int + slaTime % 5 > 5 THEN 2 ELSE 0 END * INTERVAL '1d'
        -- 处理起始日期本身就是周末的情况
        - CASE WHEN EXTRACT(ISODOW FROM created_at) IN (6,7) THEN 8 - EXTRACT(ISODOW FROM created_at)::int ELSE 0 END * INTERVAL '1d'
    )::date AS deadline
FROM proses;

如果需要高频复用该逻辑，可以封装为自定义函数：

CREATE OR REPLACE FUNCTION add_workdays(start_date date, work_days int)
RETURNS date AS $$
BEGIN
    RETURN (
        start_date
        + work_days * INTERVAL '1d'
        + (work_days + EXTRACT(ISODOW FROM start_date)::int) / 5 * 2 * INTERVAL '1d'
        + CASE WHEN EXTRACT(ISODOW FROM start_date)::int + work_days % 5 > 5 THEN 2 ELSE 0 END * INTERVAL '1d'
        - CASE WHEN EXTRACT(ISODOW FROM start_date) IN (6,7) THEN 8 - EXTRACT(ISODOW FROM start_date)::int ELSE 0 END * INTERVAL '1d'
    )::date;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

封装后查询可以简化为：

SELECT *, add_workdays(created_at::date, slaTime) AS deadline FROM proses;

小提示：如果需要截止时间精确到时分秒，和created_at的时间部分保持一致，可以把最终计算得到的截止日期加上created_at - created_at::date的时间偏移量即可。

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