如何从epoch时间戳提取当日小时并统计各时段的实例数量

问题原因 #

现有SQL的错误核心是将时间戳转成date类型时丢失了小时精度：to_timestamp(start_time)::date会把完整时间戳截断到仅保留年月日，此时再提取小时值只会返回固定值0，自然统计结果不对。同时注意需求中表存储的时间字段为event_time，需核对你代码中用到的start_time是否为字段名写错。

解决方案 #

基础修正版（仅统计有订单的小时） #

直接对转换后的完整timestamp类型提取小时即可：

SELECT EXTRACT(HOUR FROM to_timestamp(event_time)) AS hr,
       COUNT(*) AS ct
FROM x
GROUP BY hr
ORDER BY hr ASC

全量小时适配版（适配柱状图0-23全轴显示需求） #

如果需要确保X轴0到23的所有小时都有对应值（无订单的小时ct显示为0，避免图表缺段），需要先生成0-23的小时序列再左关联统计结果，以PostgreSQL为例写法如下：

WITH hours AS (
    SELECT GENERATE_SERIES(0,23) AS hr
)
SELECT h.hr,
       COALESCE(COUNT(x.event_time), 0) AS ct
FROM hours h
LEFT JOIN x ON EXTRACT(HOUR FROM to_timestamp(x.event_time)) = h.hr
GROUP BY h.hr
ORDER BY h.hr ASC

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