Epoch时间转换后按小时分组SQL执行仍按天聚合问题求助

问题根因 #

原SQL中trunc()函数未指定截断精度，默认按天截断，所以GROUP BY逻辑实际是按天分组，和SELECT部分显示的小时字段不匹配，因此无法得到按小时分组的结果。

修正后SQL代码 #

SELECT 
  COUNT(*) AS "Total",
  TO_CHAR(hour_slot, 'DD/MM/YYYY HH24') AS "Day"
FROM (
  SELECT 
    -- 计算小时级时间槽，避免重复计算转换逻辑
    TRUNC(TO_DATE('1970-01-01', 'YYYY-MM-DD') + INSERTION_TIME / 86400, 'HH24') AS hour_slot
  FROM Records
  WHERE 
    ID = 35 
    AND TO_DATE('1970-01-01', 'YYYY-MM-DD') + INSERTION_TIME / 86400 >= SYSDATE - 4
) t
GROUP BY hour_slot
ORDER BY hour_slot

核心改动说明 #

• 给trunc()函数新增第二个参数'HH24'，指定时间截断精度为小时，确保分组、排序逻辑都按小时维度执行
• 用子查询抽取出公共的Epoch时间转日期逻辑，避免原SQL中重复写3次相同的转换代码，降低后续维护成本
• 统一时间转换的计算逻辑为INSERTION_TIME / 86400，和WHERE条件的计算规则保持一致，避免不同写法导致的隐式误差

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