如何跨年度计算两个日期间的ISO_WEEK数量（非直接相减）

直接相减ISO_WEEK值计算两个日期的周数差，在跨年度场景下会出现逻辑错误，比如以下典型案例：

1. 2024-12-01（W48）与2024-12-31（W01）：直接相减得-47，预期结果为5
2. 2022-01-01（W52）与2022-01-31（W05）：直接相减得-47，预期结果为6
3. 2022-01-01（W52）与2022-12-31（W52）：直接相减得0，预期结果为53
4. 2022-12-31（W01）与2023-01-26（W04）：直接相减得-3，预期结果为5

优化方案：结合ISO年份与周数计算 #

核心思路是同时利用日期的ISO年份和ISO周数，而非单独依赖周数相减——年末的ISO周1实际属于下一个ISO年，必须通过年份维度修正差值。

以MySQL为例，优化后的SQL逻辑如下（兼容跨年度场景）：

SELECT
    start_date,
    end_date,
    -- 计算两个日期之间的ISO周总数（包含两端周）
    CASE
        WHEN end_iso_year > start_iso_year THEN
            -- 跨ISO年：中间完整年份周数 + 开始年剩余周数 + 结束年已过周数
            (end_iso_year - start_iso_year - 1) * 52 +
            (start_year_total_weeks - start_iso_week + 1) +
            end_iso_week
        WHEN end_iso_year = start_iso_year THEN
            -- 同ISO年：直接周数差 +1（包含两端）
            end_iso_week - start_iso_week + 1
        ELSE
            -- 结束日期早于开始日期，返回负数（可根据业务调整逻辑）
            -(start_iso_week - end_iso_week + 1)
    END AS iso_week_total
FROM (
    SELECT
        start_date,
        end_date,
        -- 提取ISO年份与周数（mode=3：周一为一周起始，第一周包含周四）
        FLOOR(YEAROFWEEK(start_date, 3) / 100) AS start_iso_year,
        MOD(YEAROFWEEK(start_date, 3), 100) AS start_iso_week,
        FLOOR(YEAROFWEEK(end_date, 3) / 100) AS end_iso_year,
        MOD(YEAROFWEEK(end_date, 3), 100) AS end_iso_week,
        -- 获取开始ISO年的总周数（12月28日必在当年最后一个ISO周）
        MOD(YEAROFWEEK(CONCAT(FLOOR(YEAROFWEEK(start_date, 3)/100), '-12-28'), 3), 100) AS start_year_total_weeks
    FROM your_date_table -- 替换为你的表名或日期数据集
) AS sub_query;

逻辑说明 #

1. ISO年份提取：通过YEAROFWEEK(date, 3)获取格式为YYYYWW的数值，拆分后得到ISO年份和周数
2. 跨年度处理：当结束日期的ISO年份大于开始日期时，计算中间完整年份的周数（按每年52周基础计算，若某ISO年有53周，可单独判断修正），加上开始年剩余的周数和结束年已过的周数
3. 同年度处理：直接计算周数差并加1，确保包含起始和结束的周

如果是其他数据库（如PostgreSQL），只需替换ISO年份和周数的提取函数即可：

• PostgreSQL中用EXTRACT(ISOYEAR FROM start_date)获取ISO年份，EXTRACT(WEEK FROM start_date)获取ISO周数
• 获取ISO年总周数可通过EXTRACT(WEEK FROM TO_DATE(CONCAT(EXTRACT(ISOYEAR FROM start_date), '-12-28'), 'YYYY-MM-DD'))

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