如何用更简洁的SQL语句筛选包含任意年份2月1日的日期范围记录？

核心逻辑 #

要筛选出valid_from至valid_to区间包含任意年份2月1日的记录，本质是判断是否存在某个年份Y，使得Y-02-01落在区间内。以下两种方案无需硬编码年份，扩展性强：

方案1：基于年份直接判断（直观易懂） #

通过生成起始年、结束年及下一年的2月1日，判断是否在区间内，覆盖所有可能的包含场景：

SELECT 
    *,
    CASE WHEN 
        -- 起始年份的2月1日在区间内
        valid_from <= make_date(EXTRACT(YEAR FROM valid_from)::INT, 2, 1) 
        AND valid_to >= make_date(EXTRACT(YEAR FROM valid_from)::INT, 2, 1)
        -- 结束年份的2月1日在区间内
        OR valid_from <= make_date(EXTRACT(YEAR FROM valid_to)::INT, 2, 1) 
        AND valid_to >= make_date(EXTRACT(YEAR FROM valid_to)::INT, 2, 1)
        -- 区间跨越至少一个完整的2月1日（如2023-03-01至2024-03-01，包含2024-02-01）
        OR make_date(EXTRACT(YEAR FROM valid_from)::INT + 1, 2, 1) <= valid_to
    THEN 'keep' ELSE 'discard' END AS keep
FROM your_table;

方案2：基于日期转换的通用判断（简洁高效） #

将所有日期转换为固定年份（如2000年，确保2月1日存在）的对应月日，通过判断转换后的区间是否覆盖2月1日，自动处理跨年场景：

WITH date_transform AS (
    SELECT 
        valid_from,
        valid_to,
        -- 转换为2000年的对应月日，剥离年份影响
        make_date(2000, EXTRACT(MONTH FROM valid_from)::INT, EXTRACT(DAY FROM valid_from)::INT) AS start_transformed,
        make_date(2000, EXTRACT(MONTH FROM valid_to)::INT, EXTRACT(DAY FROM valid_to)::INT) AS end_transformed,
        '2000-02-01'::DATE AS target_date
    FROM your_table
)
SELECT 
    valid_from,
    valid_to,
    CASE WHEN 
        -- 同一年份：转换后的区间包含2月1日
        (EXTRACT(YEAR FROM valid_from) = EXTRACT(YEAR FROM valid_to) 
         AND start_transformed <= target_date AND end_transformed >= target_date)
        -- 跨年份：要么起始日期在2月1日之后，要么结束日期在2月1日之前（说明区间覆盖了下一年的2月1日）
        OR (EXTRACT(YEAR FROM valid_from) < EXTRACT(YEAR FROM valid_to) 
            AND (start_transformed <= target_date OR end_transformed >= target_date))
    THEN 'keep' ELSE 'discard' END AS keep
FROM date_transform;

注意事项 #

• 上述示例基于PostgreSQL语法，若使用其他数据库（如MySQL），需替换make_date、EXTRACT等函数为对应数据库的等价函数（如MySQL用STR_TO_DATE生成指定日期）。
• 两种方案均无需维护年份列表，新增年份时无需修改代码，完全满足扩展性需求。

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