PostgreSQL生成15分钟间隔时间序列遇重复数据，求优雅解决办法

问题本质 #

当使用无时区的timestamp生成15分钟间隔序列时，夏令时转冬令时的时段（如每年10月底）本地时钟会回拨1小时，导致同一个本地时间（如02:15、02:30等）对应两个不同的UTC时间，最终插入表中出现重复的tm记录。

优雅解决方案 #

方案1：基于带时区时间戳生成并去重 #

直接用timestamptz生成连续的15分钟间隔序列，再转换为无时区timestamp，同时通过DISTINCT确保本地时间唯一。无需分段插入或事后删除，一次性生成正确数据：

INSERT INTO meter.materialized_quarters (tm)
SELECT DISTINCT (ts AT TIME ZONE 'Europe/Amsterdam')::timestamp
FROM GENERATE_SERIES(
  '1999-01-01 00:00:00'::timestamptz,
  '2030-10-30 23:45:00'::timestamptz,
  '15 minutes'::interval
) AS ts;

• 替换'Europe/Amsterdam'为你实际使用的时区（如'Asia/Shanghai'）
• GENERATE_SERIES生成的timestamptz全局唯一，转换为本地timestamp后用DISTINCT过滤重复的本地时间

方案2：用UTC生成序列再转本地时间（适合需严格物理间隔的场景） #

如果需要确保每两个时间点的物理间隔为15分钟（即使本地时间重复），但又不想存储重复的本地时间，可通过UTC序列转换后去重：

INSERT INTO meter.materialized_quarters (tm)
SELECT DISTINCT (utc_ts AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Amsterdam')::timestamp
FROM GENERATE_SERIES(
  '1999-01-01 00:00:00'::timestamp,
  '2030-10-30 23:45:00'::timestamp,
  '15 minutes'::interval
) AS utc_ts;

方案3：修改表结构使用带时区时间戳（推荐长期方案） #

如果业务允许，直接将tm字段改为timestamptz，从根源上避免时区导致的重复问题：

ALTER TABLE meter.materialized_quarters ALTER COLUMN tm TYPE timestamptz;

之后插入数据时直接生成timestamptz序列即可，无需处理重复：

INSERT INTO meter.materialized_quarters (tm)
SELECT ts
FROM GENERATE_SERIES(
  '1999-01-01 00:00:00'::timestamptz,
  '2030-10-30 23:45:00'::timestamptz,
  '15 minutes'::interval
) AS ts;

这种方式能准确记录每个时间点的实际物理时间，避免时区转换带来的歧义。

优势对比 #

相较于分段插入或事后删除，以上方案：

• 一次性完成数据生成与去重，无需拆分语句或额外操作
• 逻辑直接从时区问题根源入手，清晰易维护
• 调整时区或时间范围只需修改参数，扩展性强

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