PostgreSQL查询：近30天按小时统计温度平均值

假设你的温度数据表名为temperature_data，存储温度值的字段名为temp，可以使用以下查询语句实现需求：

SELECT
  date_trunc('hour', to_timestamp(time / 1000)) AS hour_datetime,
  round(avg(temp)::numeric, 2) AS avg_temperature
FROM temperature_data
WHERE
  to_timestamp(time / 1000) >= '2023-01-15 00:00:00'::timestamp
  AND to_timestamp(time / 1000) < '2023-02-16 00:00:00'::timestamp
GROUP BY hour_datetime
ORDER BY hour_datetime;

关键逻辑说明 #

• to_timestamp(time / 1000)：将毫秒级时间戳转换为PostgreSQL原生timestamp类型（to_timestamp默认接收秒级时间戳，因此需对原字段除以1000）。
• date_trunc('hour', ...)：将时间截断至小时维度，作为分组依据，确保每一行结果对应某一天的特定小时。
• round(avg(temp)::numeric, 2)：计算分组内的温度平均值，转换为数值类型后保留2位小数，提升结果可读性。
• WHERE子句：采用半开区间过滤时间范围，避免遗漏2023-02-15当天最后一小时的记录，确保仅包含目标日期区间内的数据。
• ORDER BY hour_datetime：让结果按时间顺序排列，便于后续查看或进一步分析。

如果你的表名、字段名与示例不同，替换对应的标识符即可。

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