使用PostgreSQL generate_series()时如何向上取整生成时间序列？

问题背景 #

使用PostgreSQL结合Grafana做数据可视化时，面板配置了以下动态时间变量：

• $__timeFrom() = '2023-10-21T00:00:00Z'
• $__timeTo() = '2023-10-21T00:25:00Z'
• $__interval = '15m'

当前用generate_series生成时间序列的查询为：

SELECT *
FROM generate_series('2023-10-21T00:00:00Z'::timestamp, '2023-10-21T00:25:00Z'::timestamp, '15m')
ORDER BY 1;

返回的时间点仅包含两个：

• 2023-10-21 00:00:00.000000
• 2023-10-21 00:15:00.000000

需要补全末尾时间点，有两种需求：

1. 包含原始结束时间2023-10-21 00:25:00.000000
2. 向上取整到间隔的下一个整时间点2023-10-21 00:30:00.000000

解决方案 #

方案1：保留原始结束时间 #

修改generate_series的结束参数，先将结束时间加上间隔值生成序列，再过滤出不超过原始结束时间的点，用DISTINCT避免结束时间刚好是间隔整数倍时出现重复：

SELECT DISTINCT ts
FROM generate_series(
  $__timeFrom()::timestamp,
  $__timeTo()::timestamp + $__interval::interval,
  $__interval::interval
) AS ts
WHERE ts <= $__timeTo()::timestamp
ORDER BY ts;

执行后返回结果：

• 2023-10-21 00:00:00.000000
• 2023-10-21 00:15:00.000000
• 2023-10-21 00:25:00.000000

方案2：向上取整到间隔整数倍时间点 #

通过计算总间隔数的向上取整值，得到最终的结束时间，再传入generate_series：

WITH params AS (
  SELECT
    $__timeFrom()::timestamp AS start_ts,
    $__timeTo()::timestamp AS end_ts,
    $__interval::interval AS step
)
SELECT generate_series(
  start_ts,
  start_ts + ceil((end_ts - start_ts) / step) * step,
  step
) AS ts
FROM params;

这个查询会把结束时间向上对齐到间隔的整数倍，执行后返回：

• 2023-10-21 00:00:00.000000
• 2023-10-21 00:15:00.000000
• 2023-10-21 00:30:00.000000

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