带时区时间戳标准差在GROUP BY中使用报错求助

错误原因 #

PostgreSQL 的 stddev() 聚合函数仅支持数值类型（整数、浮点数等），而你的 time_ 字段是 timestamp with time zone 类型，数据库不存在直接针对该时间类型的 stddev 实现，因此抛出 function stddev(timestamp with time zone) does not exist 错误。

修复代码 #

将时间类型转换为可计算的数值（比如 Unix 时间戳）后再执行标准差计算，修改后的 SQL 如下：

select  
round(stddev(extract(epoch from time_)), 2) as stddev_time_of_logs,
imei, mob_date, hour_of_day
from hourly_data 
group by imei, mob_date, hour_of_day

关键说明 #

• extract(epoch from time_)：将带时区的时间戳转换为从 1970-01-01 00:00:00 UTC 到该时间点的秒数（数值类型），这样 stddev() 就能正常计算标准差。
• 若需要以毫秒为单位计算，可改为 extract(epoch from time_) * 1000，结果单位会变为毫秒。

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