如何在PostgreSQL中生成指定数量的精准时间戳序列？

假设我们有日期：

'2017-01-01'
and
'2017-01-15'

希望在这两个日期之间生成恰好N个时间戳，例如生成7个时间点：

SELECT * FROM 
    generate_series_n(
        '2017-01-01'::timestamp,
        '2017-01-04'::timestamp,
        7
    )

期望返回结果如下：

2017-01-01 00:00:00
2017-01-01 12:00:00
2017-01-02 00:00:00
2017-01-02 12:00:00
2017-01-03 00:00:00
2017-01-03 12:00:00
2017-01-04 00:00:00

实现方案 #

PostgreSQL没有内置的generate_series_n函数，但可以通过计算时间间隔结合generate_series来实现，以下是两种实用方法：

方法一：直接用SQL语句生成 #

无需自定义函数，通过CTE定义参数后直接计算：

WITH params AS (
    SELECT 
        '2017-01-01'::timestamp AS start_ts,
        '2017-01-04'::timestamp AS end_ts,
        7 AS num_points
)
SELECT 
    start_ts + (end_ts - start_ts) * (i - 1) / (num_points - 1) AS timestamp
FROM params, generate_series(1, num_points) AS s(i);

该查询会生成包含起始、结束时间在内的N个均匀分布时间戳，核心逻辑是用总时长除以(N-1)得到每个点的间隔，再依次累加。

方法二：创建可复用的自定义函数 #

如果需要多次调用这个逻辑，可以创建generate_series_n函数：

CREATE OR REPLACE FUNCTION generate_series_n(
    start_ts timestamp,
    end_ts timestamp,
    num_points integer
) RETURNS SETOF timestamp AS $$
BEGIN
    RETURN QUERY
        SELECT start_ts + (end_ts - start_ts) * (i - 1) / (num_points - 1)
        FROM generate_series(1, num_points) AS s(i);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

创建完成后，即可像示例中那样调用：

SELECT * FROM generate_series_n('2017-01-01'::timestamp, '2017-01-04'::timestamp, 7);

注意事项 #

• 当num_points为1时，查询会直接返回起始时间，若需要特殊处理可在函数或语句中添加判断。
• 时间间隔计算基于PostgreSQL的时间类型运算，确保起始时间早于结束时间，避免出现异常结果。

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