PostgreSQL中DATE_PART返回周数53而非预期1、2的问题

场景重现 #

插入数据的SQL：

INSERT INTO runners
  ("runner_id", "registration_date")
VALUES
  (1, '2021-01-01'),
  (2, '2021-01-03'),
  (3, '2021-01-08'),
  (4, '2021-01-15');

执行的查询语句：

SELECT
    DATE_PART('WEEK', R.registration_date) AS week_num,
    COUNT(runner_id)
FROM
    pizza_runner.runners R
GROUP BY
    week_num
ORDER BY
    week_num ASC;

预期仅返回周数1和2，但实际结果出现了周数53。

原因分析 #

问题核心是PostgreSQL中DATE_PART('WEEK', ...)的周计算逻辑：

• 该函数默认的周规则并非国际通用的ISO标准，当遇到跨年的日期时，会出现归属上的偏差。
• 2021年1月1日和1月3日实际上属于2020年的第53个ISO周（ISO周规则：周一为一周起始，一年的第一个周必须包含至少4天，2020年的第53周覆盖了2020-12-28至2021-01-03），而DATE_PART('WEEK')会直接返回这个周数，而非2021年的第1周。

解决方法 #

改用PostgreSQL支持的ISO周计算即可修正这个问题，只需要把'WEEK'替换为'ISO_WEEK'：

修正后的查询：

SELECT
    DATE_PART('ISO_WEEK', R.registration_date) AS week_num,
    COUNT(runner_id)
FROM
    pizza_runner.runners R
GROUP BY
    week_num
ORDER BY
    week_num ASC;

或者使用EXTRACT函数实现同样效果：

SELECT
    EXTRACT(ISO_WEEK FROM R.registration_date) AS week_num,
    COUNT(runner_id)
FROM
    pizza_runner.runners R
GROUP BY
    week_num
ORDER BY
    week_num ASC;

执行后结果会符合预期逻辑：

• 周数1：包含2021-01-01、2021-01-03（共2条记录）
• 周数2：包含2021-01-08（1条记录）
• 周数3：包含2021-01-15（1条记录）

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