3 ms·

You can do that like this: GROUP BY date_trunc('minute', time)

• paulasmuth 11y ago
  The snippet you posted will compute an aggregation based on a fixed time interval. I.e. it will put every row into a "bucket" of per-minute granularity and then compute an aggregate function for each of those buckets bucket, taking into account only the rows that ended up in that specific bucket (i.e. only rows from that specific minute). To put it another way this is asking the question "Please give me the aggregate of some value per minute". To make my question more precise; I was trying to ask specifically about a "moving window aggregation" (e.g. a moving average over a timeseries). This is more like asking the question "Please give me every minute an aggregate based on all values in the last N minutes". To do that you need each input row to end up in more than one bucket (or have a special type of aggregation function like postgres does). For example, if you were doing a moving aggregation with a 1-minute interval ("bucket size") and a 5 minute window ("lookback"), you would need to place each row into 5 buckets: The bucket into which it belongs based on it's timestamp and the 4 previous buckets. And a vanilla SQL GROUP BY can't do that. Hope that makes sense.