PostgreSQL自定义周数计算：匹配MSSQL DATENAME(week)逻辑

要实现你描述的特定周数规则（与MSSQL默认的DATENAME(week)逻辑一致），可以通过自定义函数或纯SQL表达式实现，以下是具体方案：

核心逻辑分析 #

你需要的规则本质是：

• 一年的第1周包含该年1月1日，无论这一周有几天
• 从1月1日后的第一个周一开始，每7天为一个完整周，周数依次递增
• 2021年底的最后几天仍归属上一年的第53周，2022年初的前2天归属当年第1周

方案1：自定义PL/pgSQL函数 #

创建一个可复用的函数，直接返回符合规则的周数：

CREATE OR REPLACE FUNCTION get_mssql_week(d date)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    y integer := extract(year from d)::integer;
    first_day date := make_date(y, 1, 1);
    -- 计算当年1月1日后的第一个周一
    first_monday date := first_day + (8 - extract(isodow from first_day)::integer) % 7;
BEGIN
    -- 1月1日到第一个周一前的日期都属于第1周
    IF d < first_monday THEN
        RETURN 1;
    ELSE
        -- 从第一个周一开始，每7天递增一周数
        RETURN 1 + ((d - first_monday)::integer / 7) + 1;
    END IF;
END;
$$;

方案2：纯SQL表达式 #

如果不想创建函数，可直接使用以下SQL表达式计算单日期的周数：

WITH date_params AS (
    SELECT
        '2021-12-27'::date AS target_date, -- 替换为你需要计算的日期
        extract(year from '2021-12-27'::date)::integer AS year_num
)
SELECT
    CASE
        WHEN target_date < make_date(year_num, 1, 1) + (8 - extract(isodow from make_date(year_num, 1, 1))::int) %7 THEN 1
        ELSE 1 + ((target_date - (make_date(year_num, 1, 1) + (8 - extract(isodow from make_date(year_num, 1, 1))::int) %7))::int /7) +1
    END AS mssql_week
FROM date_params;

测试验证 #

用你的测试用例验证：

-- 2021年12月27-31日 → 53周
SELECT get_mssql_week('2021-12-27'); -- 返回53
SELECT get_mssql_week('2021-12-31'); -- 返回53

-- 2022年1月1-2日 → 1周
SELECT get_mssql_week('2022-01-01'); -- 返回1
SELECT get_mssql_week('2022-01-02'); -- 返回1

-- 2022年1月3日 → 2周
SELECT get_mssql_week('2022-01-03'); -- 返回2

-- 2022年1月10日 →3周
SELECT get_mssql_week('2022-01-10'); -- 返回3

为什么PostgreSQL内置格式符不适用 #

• IW：ISO周规则，每年第一周需包含至少4天，且周一为周起始，导致2021-12-27被算作2022年第1周，不符合需求
• WW：以1月1日为当年第1周起始，每周从周日开始，每7天递增一周，导致2022-01-03仍属于第1周，与需求不符

内容的提问来源于stack exchange，提问作者Роман Иванов