PostgreSQL如何将日期转换为当月周数而非全年周数？

原查询返回的是全年周数，要得到日期所在的当月周数，可以通过计算该日期的全年周数减去当月第一天的全年周数，再加1来实现。修改后的查询语句如下：

SELECT 
    pick.min_date,
    extract('week' from pick.min_date) - extract('week' from date_trunc('month', pick.min_date)) + 1 AS month_week
FROM account_invoice inv 
LEFT JOIN stock_picking pick ON inv.origin = pick.name 
WHERE inv.number = 'INV/2022/17359'

逻辑说明 #

• date_trunc('month', pick.min_date)：将目标日期截断到当月第一天，例如2022-08-11会转换为2022-08-01
• 用目标日期的全年周数减去当月第一天的全年周数后加1，即可得到该日期在当月的周序号

针对你的示例数据2022-08-11：

• 当月第一天2022-08-01的全年周数是31
• 目标日期的全年周数是32
• 计算结果：32 - 31 + 1 = 2，正好是当月第2周，完全符合需求

适配不同周起始规则（可选） #

PostgreSQL默认以周一作为周起始日，如果你的业务需要以周日为周起始，可以直接使用to_char函数的W格式符，它会返回日期在当月的周数（每月第一周从1号开始计算）：

SELECT 
    pick.min_date,
    to_char(pick.min_date, 'W') AS month_week
FROM account_invoice inv 
LEFT JOIN stock_picking pick ON inv.origin = pick.name 
WHERE inv.number = 'INV/2022/17359'

这个语句对2022-08-11同样会返回2。

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