如何在PostgreSQL中将周起始日设为周三并获取对应周数

PostgreSQL的DATE_PART('week', date)默认遵循ISO周规则（周一为周起始），要改成以周三为起始，需要通过日期偏移来实现：

核心逻辑 #

将目标日期往前偏移2天（周三与周一相差2天），此时原日期的周三会被转换为新日期的周一，再用DATE_PART('week', ...)计算的周数，就等价于以周三为起始的周数。同时年份也要基于偏移后的日期获取，避免跨年周的年份归属错误。

修改后的查询语句 #

select 
    m.tool_id, 
    m.module_location, 
    alarm_count,  
    recipe_id, 
    alarm_alias, 
    alarm_issuer_name,
    -- 偏移2天后计算周数，等价于周三为周起始
    DATE_PART('week', alarm_date - INTERVAL '2 days') AS week, 
    -- 基于偏移后的日期取年份，确保跨年周年份正确
    DATE_PART('year', alarm_date - INTERVAL '2 days') AS yearNo
from alarms.alarm_count a, alarms.alarm_module m, alarms.alarm_issuer ai 
where a.module_uuid = m.module_uuid and a.alarm_issuer_uuid = ai.alarm_issuer_uuid 
and m.module_uuid in (
'027909d4-12dd-4b7d-a391-847f88ee97ab',
 '212277f4-9d05-4465-95f7-a99fcb936451')
and a.alarm_date BETWEEN '2022-11-21' and '2022-12-05' 
and severity in ('Critical','Error','Fatal') 

验证示例 #

比如：

• 2022-11-23（周三）：偏移2天后是2022-11-21（周一），周数为47，对应原日期属于以周三为起始的第47周
• 2022-11-22（周二）：偏移2天后是2022-11-20（周日），属于第46周，对应原日期属于以周三为起始的第46周

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