PostgreSQL如何获取指定月份的第x个工作日

前提准备 #

首先你需要提前维护好公共节假日表，存储所有法定节假日日期，示例表结构如下（可根据实际业务调整字段）：

CREATE TABLE IF NOT EXISTS public_holidays (
    holiday_date DATE PRIMARY KEY,
    holiday_name VARCHAR(50)
);

方案1：单查询语句实现 #

适合单次查询场景，逻辑为先生成目标月份的全量日期，过滤掉周末和法定节假日后按日期排序，取序号匹配的日期即可：

WITH month_days AS (
    -- 生成目标月份所有日期，替换'2021-10-01'为你要查询的目标月份任意日期
    SELECT generate_series(
        date_trunc('month', '2021-10-01'::DATE),
        date_trunc('month', '2021-10-01'::DATE) + INTERVAL '1 month - 1 day',
        INTERVAL '1 day'
    )::DATE AS curr_date
),
work_days AS (
    SELECT curr_date,
           ROW_NUMBER() OVER (ORDER BY curr_date) AS work_day_rank
    FROM month_days
    -- 排除周末：PostgreSQL中extract(dow from date)返回0为周日、6为周六
    WHERE extract(dow from curr_date) NOT IN (0,6)
      -- 排除法定节假日
      AND curr_date NOT IN (SELECT holiday_date FROM public_holidays)
)
SELECT curr_date AS target_work_day
FROM work_days
-- 替换3为你要查询的第x个工作日序号
WHERE work_day_rank = 3;

方案2：封装为自定义函数 #

适合高频调用场景，封装后直接传参即可调用：

CREATE OR REPLACE FUNCTION get_nth_workday(p_target_month DATE, p_nth INT)
RETURNS DATE AS $$
DECLARE
    v_result DATE;
BEGIN
    WITH month_days AS (
        SELECT generate_series(
            date_trunc('month', p_target_month),
            date_trunc('month', p_target_month) + INTERVAL '1 month - 1 day',
            INTERVAL '1 day'
        )::DATE AS curr_date
    ),
    work_days AS (
        SELECT curr_date,
               ROW_NUMBER() OVER (ORDER BY curr_date) AS work_day_rank
        FROM month_days
        WHERE extract(dow from curr_date) NOT IN (0,6)
          AND curr_date NOT IN (SELECT holiday_date FROM public_holidays)
    )
    SELECT curr_date INTO v_result
    FROM work_days
    WHERE work_day_rank = p_nth;

    -- 若输入的序号超过当月工作日总数，抛出异常提示，可根据业务调整返回逻辑
    IF v_result IS NULL THEN
        RAISE EXCEPTION '当月不存在第%个工作日', p_nth;
    END IF;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql STABLE;

函数调用示例 #

-- 查询2021年10月第3个工作日，输出2021-10-05
SELECT get_nth_workday('2021-10-01', 3);

-- 查询2021年11月第3个工作日，输出2021-11-03
SELECT get_nth_workday('2021-11-01', 3);

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