postgresqlstandard-deviation

Standard deviation of timestamp with timezone - in a group by clause

Here is my code:

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

It produces the following error:

SQL Error [42883]: ERROR: function stddev(timestamp with time zone) does not exist

This is how an aggregate function is usually used, so I do not know what the issue is here. Any help is appreciated!

Solution

  • You could convert to seconds since the Epoch and back to an interval:

    make_interval(secs => stddev(extract (epoch FROM _time)))