如何在PostgreSQL中用generate_series生成间隔减1秒的时间序列

你可以通过拆分基础时间序列为「区间起始点」和「区间结束点」的方式实现需求，参考SQL如下：

-- 可自行修改CTE中的参数适配不同的时间范围、步长
WITH time_params AS (
    SELECT
        -- 起始时间，对应你原SQL的开始值
        ('02:00:00'::time + '2021-09-25'::date)::timestamp AS start_ts,
        -- 结束时间，对应你原SQL的结束值
        ('04:00:00'::time + '2021-09-25'::date)::timestamp AS end_ts,
        -- 步长
        interval '15m' AS step_len
)
SELECT output_ts AS generate_series
FROM time_params,
     -- 先生成基础的15分钟间隔整点序列
     generate_series(start_ts, end_ts, step_len) AS base_ts
-- 每个整点拆成两个值：整点本身、整点+步长-1秒
CROSS JOIN LATERAL (
    VALUES
        (base_ts),
        (base_ts + step_len - interval '1 second')
) AS t(output_ts)
-- 过滤掉超出结束时间的结果
WHERE output_ts < end_ts
-- 保证时间顺序正确
ORDER BY output_ts;

逻辑说明 #

• 先用generate_series生成所有步长间隔的基础整点时间
• 通过CROSS JOIN LATERAL配合VALUES语法，将每个基础整点拆分为两个时间点：区间起始的整点、区间结束的「步长整点减1秒」
• 最后过滤掉所有大于等于结束时间的数值，避免出现超出需求范围的时间点

如果你需要直接输出你示例中的带UTC标识的JSON格式，可以使用如下SQL：

WITH time_params AS (
    SELECT
        ('02:00:00'::time + '2021-09-25'::date)::timestamptz AT TIME ZONE 'UTC' AS start_ts,
        ('04:00:00'::time + '2021-09-25'::date)::timestamptz AT TIME ZONE 'UTC' AS end_ts,
        interval '15m' AS step_len
)
SELECT json_agg(
    json_build_object(
        'generate_series', 
        to_char(output_ts AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
    ) 
    ORDER BY output_ts
) AS result
FROM time_params,
     generate_series(start_ts, end_ts, step_len) AS base_ts
CROSS JOIN LATERAL (
    VALUES
        (base_ts),
        (base_ts + step_len - interval '1 second')
) AS t(output_ts)
WHERE output_ts < end_ts;

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