如何在Presto中将时间戳向上/向下取整至最近30分钟间隔

核心实现逻辑 #

Presto可通过内置时间函数，按需求实现30分钟间隔的向上、向下、四舍五入取整，以下是对应方案：

1. 向上取整（适配你示例的需求） #

你给出的示例2021-08-23 17:46:06要得到2021-08-23 18:00属于向上取整场景，实现代码如下：

SELECT date_add('minute', 
               CASE WHEN minute(your_timestamp) % 30 = 0 THEN 0 
                    ELSE 30 - (minute(your_timestamp) % 30) 
               END, 
               date_trunc('hour', your_timestamp)) AS rounded_up_30min
-- 测试数据，实际使用替换为你的表和时间戳字段即可
FROM (SELECT timestamp '2021-08-23 17:46:06' AS your_timestamp) t;

运行结果为2021-08-23 18:00:00，如果需要去掉秒级展示，可额外套入date_format函数调整输出格式。

2. 向下取整 #

如果需要得到2021-08-23 17:30的取整结果，用以下代码实现：

SELECT date_add('minute', 
               floor(minute(your_timestamp) / 30) * 30, 
               date_trunc('hour', your_timestamp)) AS rounded_down_30min
FROM (SELECT timestamp '2021-08-23 17:46:06' AS your_timestamp) t;

3. 四舍五入到最近的30分钟间隔 #

如果需要按分钟数判断，小于15分取上一个半小时节点、大于等于15分取下一个半小时节点，实现如下：

SELECT date_add('minute', 
               round(minute(your_timestamp) / 30) * 30, 
               date_trunc('hour', your_timestamp)) AS rounded_nearest_30min
FROM (SELECT timestamp '2021-08-23 17:46:06' AS your_timestamp) t;

高版本简化写法（Presto 0.220+支持） #

版本符合要求的场景下可以直接用date_bin函数一步实现，代码更简洁：

• 向上取整：SELECT date_bin('30 minutes', your_timestamp + interval '29' minute, timestamp '1970-01-01 00:00:00') FROM 你的表;
• 向下取整：SELECT date_bin('30 minutes', your_timestamp, timestamp '1970-01-01 00:00:00') FROM 你的表;

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