PostgreSQL：使用generate_series时排除午夜结束的最后一天

可以实现这个需求，核心是根据结束时间的小时部分动态调整generate_series的结束参数：

• 当结束时间的小时不为0时，直接使用原结束日期作为序列终点
• 当结束时间的小时为0时，将结束日期减1天作为序列终点

通用实现SQL #

使用case表达式结合extract(hour from ...)来判断并调整结束日期：

SELECT generate_series(
    '2023-01-06 00:00:00+00'::date,
    CASE 
        WHEN extract(hour from '2023-02-03 00:00:00+00'::timestamp with time zone) = 0
        THEN '2023-02-03 00:00:00+00'::date - interval '1 day'
        ELSE '2023-02-03 00:00:00+00'::date
    END,
    '1 day'
) AS date_series;

分场景验证 #

1. 结束时间非午夜（10:00:00）
   执行以下语句：

SELECT generate_series(
    '2023-01-06 10:00:00+00'::date,
    CASE 
        WHEN extract(hour from '2023-02-03 10:00:00+00'::timestamp with time zone) = 0
        THEN '2023-02-03 10:00:00+00'::date - interval '1 day'
        ELSE '2023-02-03 10:00:00+00'::date
    END,
    '1 day'
) AS date_series;

结果序列首元素为2023-01-06，末元素为2023-02-03，符合预期。

2. 结束时间为午夜（00:00:00）
   执行以下语句：

SELECT generate_series(
    '2023-01-06 00:00:00+00'::date,
    CASE 
        WHEN extract(hour from '2023-02-03 00:00:00+00'::timestamp with time zone) = 0
        THEN '2023-02-03 00:00:00+00'::date - interval '1 day'
        ELSE '2023-02-03 00:00:00+00'::date
    END,
    '1 day'
) AS date_series;

结果序列首元素为2023-01-06，末元素为2023-02-02，满足需求。

封装成自定义函数（可选） #

如果需要多次调用，可封装成函数简化使用：

CREATE OR REPLACE FUNCTION generate_date_series(start_ts timestamptz, end_ts timestamptz)
RETURNS SETOF date AS $$
BEGIN
    RETURN QUERY
    SELECT generate_series(
        start_ts::date,
        CASE 
            WHEN extract(hour from end_ts) = 0
            THEN end_ts::date - interval '1 day'
            ELSE end_ts::date
        END,
        '1 day'
    );
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT * FROM generate_date_series('2023-01-06 00:00:00+00', '2023-02-03 00:00:00+00');

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