PostgreSQL中如何将时间戳向上取整至最近的10分钟？

要实现将时间戳向上取整到下一个完整的10分钟（包括刚好是10分钟整点的情况，如10:00:00需取整到10:10:00），可以使用以下SQL方案：

方案一：通过总秒数计算取整 #

SELECT 
  input_timestamp,
  date_trunc('hour', input_timestamp) + 
  INTERVAL '10 minutes' * CEIL((EXTRACT(MINUTE FROM input_timestamp) * 60 + EXTRACT(SECOND FROM input_timestamp)) / 600)
AS rounded_timestamp
FROM (
  VALUES
    ('2023-07-01 10:00:00'::TIMESTAMP),
    ('2023-07-01 10:00:01'::TIMESTAMP),
    ('2023-07-01 10:01:00'::TIMESTAMP),
    ('2023-07-01 10:05:00'::TIMESTAMP),
    ('2023-07-01 10:09:59'::TIMESTAMP)
) AS t(input_timestamp);

逻辑说明 #

1. 用date_trunc('hour', ...)将原时间截断到整点小时；
2. 把当前时间的分钟、秒转换为总秒数，除以10分钟对应的600秒；
3. 通过CEIL()向上取整，得到需要在整点小时基础上添加的10分钟间隔数；
4. 间隔数乘以10分钟后加到整点小时时间上，得到最终取整结果。

方案二：分情况处理整点与非整点 #

SELECT 
  input_timestamp,
  CASE
    WHEN EXTRACT(MINUTE FROM input_timestamp) % 10 = 0 AND EXTRACT(SECOND FROM input_timestamp) = 0
    THEN input_timestamp + INTERVAL '10 minutes'
    ELSE date_trunc('hour', input_timestamp) + INTERVAL '10 minutes' * ((EXTRACT(MINUTE FROM input_timestamp)::INT / 10) + 1)
  END AS rounded_timestamp
FROM (
  VALUES
    ('2023-07-01 10:00:00'::TIMESTAMP),
    ('2023-07-01 10:00:01'::TIMESTAMP),
    ('2023-07-01 10:01:00'::TIMESTAMP),
    ('2023-07-01 10:05:00'::TIMESTAMP),
    ('2023-07-01 10:09:59'::TIMESTAMP)
) AS t(input_timestamp);

逻辑说明 #

1. 先判断当前时间是否为10分钟整点（分钟是10的倍数且秒数为0），若是则直接加10分钟；
2. 非整点情况先截断到小时，计算当前分钟所在的10分钟区间索引，加1后得到下一个区间的索引，乘以10分钟后加到整点小时时间上。

两种方案执行后均会得到符合预期的结果：

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