Aggregate time-series data with time_bucket
time_bucket function helps you group your data, so you can perform
aggregate calculations over arbitrary time intervals. It is usually used
in combination with
GROUP BY for this purpose.
This section shows examples of
time_bucket use. To learn how time buckets
work, see the section that explains time buckets.
Group data into time buckets and calculate a summary value for a column. For
example, calculate the average daily temperature in a table named
weather_conditions. The table has a time column named
time and a
SELECT time_bucket('1 day', time) AS bucket, avg(temperature) AS avg_temp FROM weather_conditions GROUP BY bucket ORDER BY bucket ASC;
time_bucket returns the start time of the bucket. In this example, the first
bucket starts at midnight on November 15, 2016, and aggregates all the data from
bucket | avg_temp -----------------------+--------------------- 2016-11-15 00:00:00+00 | 68.3704391666665821 2016-11-16 00:00:00+00 | 67.0816684374999347
By default, the
time_bucket column shows the start time of the bucket. If you
prefer to show the end time, you can shift the displayed time using a
mathematical operation on time.
For example, calculate the minimum and maximum CPU usage for 5-minute intervals.
Show the end of time of the interval. The example table is named
has a time column named
time and a CPU usage column named
SELECT time_bucket('5 min', time) + '5 min' AS bucket, min(cpu), max(cpu) FROM metrics GROUP BY bucket ORDER BY bucket DESC;
The addition of
+ '5 min' changes the displayed timestamp to the end of the
bucket. It doesn't change the range of times spanned by the bucket.
To change the time range spanned by the buckets, use the
which takes an
INTERVAL argument. A positive offset shifts the start and end
time of the buckets later. A negative offset shifts the start and end time of
the buckets earlier.
For example, calculate the average CPU usage for 5-hour intervals. Shift the start and end times of all buckets 1 hour later:
SELECT time_bucket('5 hours', time, '1 hour'::INTERVAL) AS bucket, avg(cpu) FROM metrics GROUP BY bucket ORDER BY bucket DESC;
Time buckets are usually used together with
GROUP BY to aggregate data. But
you can also run
time_bucket on a single time value. This is useful for
testing and learning, because you can see what bucket a value falls into.
For example, to see the 1-week time bucket into which January 5, 2021 would fall, run:
SELECT time_bucket(INTERVAL '1 week', TIMESTAMP '2021-01-05');
The function returns
2021-01-04 00:00:00. That is the start time of the
time bucket: the Monday of that week, at midnight.
Found an issue on this page?Report an issue!