PostgreSQL使用generate_series生成15分钟间隔序列如何取到23:59最后一条记录

问题根因 #

PostgreSQL 的 generate_series 时间生成规则为仅返回小于等于设定结束值、且符合步进间隔的时间点。你使用15分钟作为步进时，生成的时间点分钟固定为00、15、30、45，最后一个满足≤2021-10-03 23:59:00的时间点为23:45:00，再加15分钟得到的次日00:00超出结束时间范围，因此不会返回。而15分钟步进本身无法生成23:59的时间点，因此需要通过调整关联逻辑实现覆盖23:45~23:59区间的记录。

解决方案 #

方案1：调整关联查询的时间区间判断（最常用） #

无需修改生成序列的逻辑，仅在关联业务表时将等值匹配改为区间匹配，即可覆盖每个15分钟时段内的所有记录，包括23:45~23:59的部分：

SELECT t."time", m.*
FROM 
  generate_series(
    ('2021-10-03 00:00:00')::timestamp,
    ('2021-10-03 23:59:00')::timestamp,
    interval '15m'
  ) AS t("time")
LEFT JOIN 你的主表 m 
  ON m.你的时间字段 >= t."time" 
  AND m.你的时间字段 < t."time" + interval '15m'

方案2：手动指定最后一个时段的结束边界 #

如果你需要明确将最后一个时段的结束时间限制在当日23:59:59，避免匹配到次日0点的记录，可以单独处理最后一个区间的上限：

SELECT 
  t."time",
  -- 最后一个时段的结束时间设置为当日最后一秒
  CASE 
    WHEN t."time" = '2021-10-03 23:45:00' THEN '2021-10-03 23:59:59'::timestamp
    ELSE t."time" + interval '15m' 
  END AS time_range_end
FROM generate_series(
  '2021-10-03 00:00:00'::timestamp,
  '2021-10-03 23:45:00'::timestamp,
  interval '15m'
) AS t("time")

关联业务表时使用：m.你的时间字段 BETWEEN t."time" AND t.time_range_end即可。

方案3：调整序列结束时间后过滤多余值 #

如果你的业务场景确实需要生成包含23:59的时间点（例如非固定步进的特殊需求），可以直接在生成的序列后追加23:59的时间点：

SELECT "time"
FROM generate_series(
  '2021-10-03 00:00:00'::timestamp,
  '2021-10-03 23:45:00'::timestamp,
  interval '15m'
  -- 额外追加23:59的时间点
  UNION ALL SELECT '2021-10-03 23:59:00'::timestamp
) AS t("time")
ORDER BY "time"

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