PostgreSQL如何实现从2021年1月1日起自定义计算年度周序号

你使用EXTRACT(WEEK)获取到的是ISO标准周数值，ISO周规则要求每年第一个周必须包含至少4个公历年天数，因此年初的日期经常会被划入上一年的最后一周，这就是你查询2021-01-01得到周数53的原因。

要实现以每年1月1日作为第1周起始、每7天累计周数的需求，直接用日期差值计算即可，逻辑如下：

1. 先取当前日期对应的当年1月1日的日期
2. 计算当前日期和当年1月1日的间隔天数
3. 间隔天数做整数除以7的运算后加1，即为自定义周数

基础实现代码 #

-- 返回数字类型的自定义周数
SELECT 
  (('2021-01-01'::date - date_trunc('year', '2021-01-01'::date)::date) / 7) + 1 AS custom_week,
  EXTRACT(YEAR FROM '2021-01-01'::date) AS year;

执行后返回结果为 1 | 2021，符合基础预期。

补零格式化版本 #

如果你需要输出两位带前导零的周数格式（如01而非1），可以使用TO_CHAR做格式化处理：

-- 返回两位字符串格式的自定义周数
SELECT 
  TO_CHAR(
    (('2021-01-01'::date - date_trunc('year', '2021-01-01'::date)::date) / 7) + 1,
    'FM00'
  ) AS custom_week,
  EXTRACT(YEAR FROM '2021-01-01'::date) AS year;

执行后返回结果为 01 | 2021，完全匹配你的需求。

你可以代入其他日期验证规则：

• 2021-01-08：间隔天数为7，7/7=1，加1得2，对应第2周
• 2021-01-29：间隔天数为28，28/7=4，加1得5，对应第5周

封装为可复用函数 #

如果需要频繁使用该计算逻辑，可以封装为Postgres自定义函数：

CREATE OR REPLACE FUNCTION get_custom_week(input_date date) 
RETURNS text AS $$
BEGIN
  RETURN TO_CHAR(
    (input_date - date_trunc('year', input_date)::date) / 7 + 1,
    'FM00'
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用方式：

SELECT get_custom_week('2021-01-01'), EXTRACT(YEAR FROM '2021-01-01'::date);

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