PostgreSQL中按月份拆分日期区间的实现求助

针对你需要将跨月的日期区间拆分为每月子区间的需求，可通过generate_series结合日期函数实现，以下是具体解决方案：

1. 测试数据准备（若已有表可跳过） #

假设你的表名为date_ranges，字段为字符串类型的日期（DD/MM/YYYY格式），先创建测试表并插入数据：

CREATE TABLE date_ranges (
    start_at VARCHAR(10),
    end_at VARCHAR(10)
);

INSERT INTO date_ranges VALUES ('17/1/2023', '15/3/2023');

2. 拆分SQL语句 #

WITH monthly_dates AS (
    SELECT
        generate_series(
            date_trunc('month', to_date(start_at, 'DD/MM/YYYY'))::DATE,
            date_trunc('month', to_date(end_at, 'DD/MM/YYYY'))::DATE,
            '1 month'::INTERVAL
        ) AS month_start
    FROM date_ranges
)
SELECT
    -- 确定每个子区间的起始日期：首月用原start_at，其他月份用当月第一天
    CASE
        WHEN md.month_start = date_trunc('month', to_date(dr.start_at, 'DD/MM/YYYY'))::DATE
            THEN to_date(dr.start_at, 'DD/MM/YYYY')
            ELSE md.month_start
    END AS start_at,
    -- 确定每个子区间的结束日期：末月用原end_at，其他月份用当月最后一天
    CASE
        WHEN (md.month_start + INTERVAL '1 month' - INTERVAL '1 day') > to_date(dr.end_at, 'DD/MM/YYYY')
            THEN to_date(dr.end_at, 'DD/MM/YYYY')
            ELSE (md.month_start + INTERVAL '1 month' - INTERVAL '1 day')::DATE
    END AS end_at
FROM date_ranges dr
CROSS JOIN monthly_dates md
ORDER BY start_at;

3. 关键逻辑说明 #

• date_trunc('month', ...)：将日期截断到当月第一天，用于确定区间覆盖的所有月份。
• generate_series：生成从起始月份到结束月份的每月第一天序列，确保每个月份都有对应的行。
• CASE语句：分别处理首月、末月的特殊情况，首月起始用原日期，末月结束用原日期，中间月份取整月的起止。

注意事项 #

如果你的start_at和end_at字段已经是DATE类型，可直接去掉to_date(..., 'DD/MM/YYYY')转换，直接使用字段名即可。

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