SQL如何将timestamp取整为30分钟间隔实现数据分组查询

解决方案 #

你需要先把created时间戳按30分钟间隔向下取整，再替换原来的created字段作为分组维度即可，不同数据库的时间取整写法略有差异，以下是通用逻辑和主流数据库的实现示例：

核心逻辑 #

把时间戳的分钟数对30取模，减去余数就能得到所属的30分钟区间起始时间，比如09:12会取整到09:00，09:35会取整到09:30。

主流数据库实现示例 #

1. MySQL 写法

select 
    -- 生成30分钟区间起始时间戳
    DATE_FORMAT(
        FROM_UNIXTIME(UNIX_TIMESTAMP(created) - MOD(UNIX_TIMESTAMP(created), 30*60)),
        '%Y-%m-%d %H:%i:00'
    ) as time_window_30min,
    currency,
    MAX(CASE WHEN type = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
    MAX(CASE WHEN type = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by time_window_30min, currency

2. PostgreSQL 写法

select 
    -- 生成30分钟区间起始时间戳
    date_trunc('hour', created) + date_part('minute', created)::int / 30 * interval '30 min' as time_window_30min,
    currency,
    MAX(CASE WHEN type = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
    MAX(CASE WHEN type = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by time_window_30min, currency

3. SQL Server 写法

select 
    -- 生成30分钟区间起始时间戳
    DATEADD(MINUTE, DATEDIFF(MINUTE, 0, created) / 30 * 30, 0) as time_window_30min,
    currency,
    MAX(CASE WHEN type = 'Bank A' THEN rate ELSE null END) as bank_a_rate,
    MAX(CASE WHEN type = 'Bank B' THEN rate ELSE null END) as bank_b_rate
from rates
group by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, created) / 30 * 30, 0), currency

补充说明 #

• 如果你需要取区间结束时间而不是起始时间，只需要在上面的取整逻辑基础上再加30分钟即可
• 如果某30分钟区间内同一家银行有多个汇率数据，这里用MAX取的是区间内最高值，你也可以根据业务需求换成AVG取平均值、MIN取最低值，或者用窗口函数取区间最后一条数据的汇率

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