Home How to bin timestamp data into buckets of n minutes in postgres

# How to bin timestamp data into buckets of n minutes in postgres

user963
1#
user963 Published in April 21, 2018, 3:26 pm
 I have the following query which works, binning timestamped "observations" into buckets whose boundaries are defined by the bins table: SELECT count(id), width_bucket( time :: TIMESTAMP, (SELECT ARRAY(SELECT start_time FROM bins WHERE owner_id = 'some id' ORDER BY start_time ASC) :: TIMESTAMP[]) ) bucket FROM observations WHERE owner_id = 'some id' GROUP BY bucket ORDER BY bucket;  I would like to modify this to allow for querying arbitrary n-minute bins starting from a specified timestamp, rather than having to pull from from an actual "bins" table. That is, given a start time, a "bin width" in minutes, and a number of bins, is there a way I can generate the array of timestamps to pass into the width_bucket function? Alternatively, is there a different/simpler approach to get the same results?
 You need to login account before you can post.
Processed in 0.299579 second(s) , Gzip On .