PostgreSQL按日、月筛选日期范围（忽略年份）查询无结果问题

我有一张包含start_date字段的表，需要通过from_date和to_date两个筛选条件，查询所有start_date的日、月处于这两个日期的日、月范围内的记录（忽略年份）。例如当from_date为'2023-12-25'、to_date为'2023-02-07'时，需返回所有12月25日至次年2月7日期间的记录。我参考相关方案编写了如下SQL查询，但未获取到任何数据：

SELECT *
FROM mytable 
WHERE status = 'Active'
AND (
    (
        EXTRACT(MONTH FROM from_date::DATE) <= EXTRACT(MONTH FROM to_date::DATE) 
        AND (TO_CHAR(start_date::date, 'MM/DD') BETWEEN TO_CHAR(from_date::date, 'MM/DD') AND TO_CHAR(to_date::date, 'MM/DD'))
    ) 
    OR (
        EXTRACT(MONTH FROM from_date::DATE) > EXTRACT(MONTH FROM to_date::DATE) 
        AND (
            (TO_CHAR(start_date::date, 'MM/DD') BETWEEN TO_CHAR(from_date::date, 'MM/DD') AND '31/12')
            OR (TO_CHAR(start_date::date, 'MM/DD') BETWEEN '01/01' AND TO_CHAR(to_date::date, 'MM/DD'))
        )
    )
);

问题分析 #

原SQL的核心问题在于使用'MM/DD'格式的字符串进行范围比较，虽然逻辑框架正确，但字符串比较可能受数据库排序规则影响出现隐性错误，此外部分场景下日期格式转换的细节疏漏也会导致条件不匹配，最终返回空结果。

修正方案 #

改用**将月日转换为整数（格式为MMDD，如1225代表12月25日）**的方式进行比较，这种方式更直观且能避免字符串比较的潜在问题：

SELECT *
FROM mytable 
WHERE status = 'Active'
AND (
    -- 当from的月份小于等于to的月份时，直接匹配月日范围
    (
        EXTRACT(MONTH FROM from_date::DATE) <= EXTRACT(MONTH FROM to_date::DATE)
        AND TO_CHAR(start_date::DATE, 'MMDD')::INT BETWEEN TO_CHAR(from_date::DATE, 'MMDD')::INT AND TO_CHAR(to_date::DATE, 'MMDD')::INT
    )
    OR
    -- 当from的月份大于to的月份时，匹配"from到年底"或"年初到to"的范围
    (
        EXTRACT(MONTH FROM from_date::DATE) > EXTRACT(MONTH FROM to_date::DATE)
        AND (
            TO_CHAR(start_date::DATE, 'MMDD')::INT >= TO_CHAR(from_date::DATE, 'MMDD')::INT
            OR TO_CHAR(start_date::DATE, 'MMDD')::INT <= TO_CHAR(to_date::DATE, 'MMDD')::INT
        )
    )
);

特殊情况优化 #

如果需要处理闰年2月29日的特殊场景（避免非闰年中不存在该日期导致的匹配遗漏），可以添加额外判断逻辑，将2月29日等效为2月28日匹配：

SELECT *
FROM mytable 
WHERE status = 'Active'
AND (
    (
        EXTRACT(MONTH FROM from_date::DATE) <= EXTRACT(MONTH FROM to_date::DATE)
        AND (
            TO_CHAR(start_date::DATE, 'MMDD')::INT BETWEEN TO_CHAR(from_date::DATE, 'MMDD')::INT AND TO_CHAR(to_date::DATE, 'MMDD')::INT
            -- 闰年2月29日匹配非闰年2月28日
            OR (TO_CHAR(start_date::DATE, 'MMDD') = '0228' AND TO_CHAR(from_date::DATE, 'MMDD') <= '0229' AND TO_CHAR(to_date::DATE, 'MMDD') >= '0229')
        )
    )
    OR
    (
        EXTRACT(MONTH FROM from_date::DATE) > EXTRACT(MONTH FROM to_date::DATE)
        AND (
            TO_CHAR(start_date::DATE, 'MMDD')::INT >= TO_CHAR(from_date::DATE, 'MMDD')::INT
            OR TO_CHAR(start_date::DATE, 'MMDD')::INT <= TO_CHAR(to_date::DATE, 'MMDD')::INT
            -- 跨年度场景下的闰年2月29日匹配
            OR (TO_CHAR(start_date::DATE, 'MMDD') = '0228' AND TO_CHAR(to_date::DATE, 'MMDD') >= '0229')
            OR (TO_CHAR(start_date::DATE, 'MMDD') = '0228' AND TO_CHAR(from_date::DATE, 'MMDD') <= '0229')
        )
    )
);

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