生成指定日期区间内的时间戳序列问题求助

问题分析 #

原SQL的核心问题是：generate_series按1月步长生成的序列无法覆盖最大日期所在的非完整月份，且统一输出整月区间，导致该月份的实际结束时间被错误替换为月末，甚至完全缺失该月份记录。

修改后的SQL #

WITH params AS (
    -- 定义固定参数：最小日期、最大日期（当前日期减1年）
    SELECT 
        '2023-06-19 09:54:23.000'::timestamp(0) AS min_date,
        now()::timestamp(0) - interval '1 year' AS max_date
),
-- 生成最大日期所在月份的非完整区间记录
last_month_record AS (
    SELECT
        date_trunc('month', max_date)::timestamp(0) AS start_date,
        -- 取最大日期当天的最后一刻（23:59:59.999）
        (max_date::date + interval '1 day -1 millisecond')::timestamp(0) AS end_date
    FROM params
),
-- 生成从最小日期当月到最大日期前一个月的所有完整自然月区间
full_months AS (
    SELECT
        date_trunc('month', series_date)::timestamp(0) AS start_date,
        (date_trunc('month', series_date) + interval '1 month -1 millisecond')::timestamp(0) AS end_date
    FROM params,
         generate_series(
             -- 从最小日期的当月第一天开始
             date_trunc('month', min_date),
             -- 到最大日期所在月份的前一个月结束
             date_trunc('month', max_date) - interval '1 month',
             '1 month'
         ) AS series_date
)
-- 合并两部分结果，按起始日期降序排列
SELECT * FROM last_month_record
UNION ALL
SELECT * FROM full_months
ORDER BY start_date DESC;

逻辑说明 #

1. params CTE：集中定义最小日期和最大日期，方便后续修改和维护。
2. last_month_record：单独处理最大日期所在月份，起始为当月第一天，结束为最大日期当天的最后一刻，确保符合首条记录的特殊要求。
3. full_months：生成所有完整自然月的区间，范围从最小日期的当月第一天，到最大日期所在月份的前一个月，避免重复覆盖非完整月份。
4. 合并排序：将非完整月记录和完整月记录合并，按起始日期降序排列，与原SQL的输出顺序一致。

特殊场景验证 #

• 当当前日期为2024-12-22时，最大日期为2023-12-22，首条记录为：

  start_date               end_date
  ------------------------ ------------------------
  2023-12-01 00:00:00      2023-12-22 23:59:59.999
  

• 当当前日期为2024-01-01时，最大日期为2023-01-01，首条记录为：

  start_date               end_date
  ------------------------ ------------------------
  2023-01-01 00:00:00      2023-01-01 23:59:59.999
  

• 原问题中缺失的2023-12-01至2023-12-09记录，会由last_month_record正确生成。

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