Aggregate time-series data with time_bucket
The 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 by time buckets and calculate a summary value
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
temperature
column.
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
that day:
bucket | avg_temp
-----------------------+---------------------
2016-11-15 00:00:00+00 | 68.3704391666665821
2016-11-16 00:00:00+00 | 67.0816684374999347
Group data by time buckets and show the end time of the bucket
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 metrics
. It
has a time column named time
and a CPU usage column named cpu
.
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.
Group data by time buckets and change the time range of the bucket
To change the time range spanned by the buckets, use the offset
parameter,
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;
Calculate the time bucket of a single value
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!Keywords