PostgreSQL中按365天拆分日期区间的实现方法

需求概述 #

需要将任意YYYY-MM-DD格式的起始/结束日期，拆分为多个连续的子区间，要求：

• 每个子区间的跨度不超过365天
• 第一个子区间的起始为原start_date，最后一个子区间的结束为原end_date
• 子区间连续：前一个区间的end_dt + 1天 = 后一个区间的start_dt
• 若原始日期间隔不足365天，直接输出单个区间

示例输入输出 #

输入 #

start_date: '2019-06-25' 
end_date: '2024-10-15'

期望输出 #

start_dt      end_dt
---------------------------------
'2019-06-25'|'2020-06-24'
'2020-06-25'|'2021-06-25'
'2021-06-26'|'2022-06-26'
'2022-06-27'|'2023-06-27'
'2023-06-28'|'2024-10-15'

解决方案：递归CTE实现（SQL） #

LAG函数属于窗口函数，无法动态生成未知数量的区间，递归CTE是更适合的方案，它可以循环生成子区间直到覆盖整个原始日期范围。

通用SQL代码（以PostgreSQL为例） #

WITH RECURSIVE date_intervals AS (
    -- 初始化第一个子区间
    SELECT
        start_date AS start_dt,
        -- 取start_date+364天（即365天跨度）和原end_date的较小值作为结束
        LEAST(start_date + INTERVAL '364 days', end_date) AS end_dt,
        end_date AS original_end
    FROM (
        -- 替换这里的start_date和end_date为你的输入值
        SELECT
            '2019-06-25'::DATE AS start_date,
            '2024-10-15'::DATE AS end_date
    ) AS input_dates

    UNION ALL

    -- 递归生成后续子区间
    SELECT
        end_dt + INTERVAL '1 day' AS start_dt,
        LEAST(end_dt + INTERVAL '365 days', original_end) AS end_dt,
        original_end
    FROM date_intervals
    -- 当当前区间的结束还没到原end_date时继续递归
    WHERE end_dt < original_end
)
-- 按起始日期排序输出
SELECT start_dt, end_dt
FROM date_intervals
ORDER BY start_dt;

代码说明 #

1. 初始化阶段：生成第一个子区间，用LEAST确保不会超过原始结束日期
2. 递归阶段：每次以上一个区间的结束日期加1天作为新的起始，同样用LEAST控制结束日期不超过原始结束日期，直到覆盖整个范围
3. 排序输出：保证区间按时间顺序排列

适配其他SQL方言 #

不同数据库的日期函数语法略有差异，调整对应部分即可：

• MySQL：将INTERVAL 'X days'替换为DATE_ADD(字段, INTERVAL X DAY)，例如DATE_ADD(start_date, INTERVAL 364 DAY)
• SQL Server：将INTERVAL 'X days'替换为DATEADD(day, X, 字段)，例如DATEADD(day, 364, start_date)

测试场景验证 #

场景1：间隔1年4个月

输入：

SELECT '2019-06-25'::DATE AS start_date, '2020-10-25'::DATE AS end_date

输出：

start_dt    | end_dt
------------|------------
2019-06-25  | 2020-06-24
2020-06-25  | 2020-10-25

场景2：间隔不足365天

输入：

SELECT '2024-01-01'::DATE AS start_date, '2024-05-01'::DATE AS end_date

输出：

start_dt    | end_dt
------------|------------
2024-01-01  | 2024-05-01

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