PostgreSQL标准年度周转以色列年度周的查询修正问题

你的当前israel_week计算逻辑错误，直接用年度天数除以7的方式无法正确对应周日至周六的周起始规则，以下是两种修正方案：

方案一：贴合以色列周的历法规则（年度为ISO年度） #

该方案遵循以色列周的标准定义：周日为一周首日，年度以包含该周多数天数的年份为准（和ISO年度逻辑一致，仅周起始日不同）。

select
    data_date, 
    CONCAT(to_char(data_date::date, 'IYYY'), 'W', to_char(data_date::date, 'IW')) as standard_week,
    -- 修正后的以色列周计算
    CONCAT(
        to_char(data_date::date + interval '1 day', 'IYYY'),
        'W',
        to_char(data_date::date + interval '1 day', 'IW')
    ) as israel_week
from
    data
where
    data_date >= '2022-01-01'
    and data_date <= '2022-01-31'
group by data_date, standard_week, israel_week
order by data_date

逻辑说明 #

PostgreSQL的IYYY（ISO年度）和IW（ISO周数）默认以周一为周起始日，将日期加1天后：

• 原周日变为周一，此时计算出的ISO周数正好对应以色列周的周数
• 年度自动匹配该以色列周所属的年份（比如2022年1月1日周六，会被归为2021年最后一个以色列周）

方案二：强制按公历年度分组（周数从当年第一个周日开始计数） #

如果需要将1月1日所在的周强制归为当前公历年度的第1周，不管该周是否跨年度，可以使用此方案：

select
    data_date, 
    CONCAT(to_char(data_date::date, 'IYYY'), 'W', to_char(data_date::date, 'IW')) as standard_week,
    -- 强制按公历年度计算的以色列周
    CONCAT(
        extract(year from data_date::date)::text,
        'W',
        lpad(
            ((extract(doy from data_date::date) - 1 - (extract(isodow from data_date::date) % 7)) / 7 + 1)::text,
            2,
            '0'
        )
    ) as israel_week
from
    data
where
    data_date >= '2022-01-01'
    and data_date <= '2022-01-31'
group by data_date, standard_week, israel_week
order by data_date

逻辑说明 #

• extract(isodow from data_date)返回1（周一）到7（周日）的数值，%7转换为周日=0、周一=1...周六=6的周内偏移
• 通过(doy-1 - 周内偏移)计算出该日期所在以色列周的起始日对应的0-base年度天数，再除以7得到周序号，加1后得到公历年度内的以色列周数
• lpad用于补全两位数字格式，和标准周的显示保持一致

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