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

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.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299579 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO