This is a more powerful version of the standard PostgreSQL
It allows for arbitrary time intervals instead of the second, minute, hour, etc.
date_trunc. The return value is the bucket's start time.
Below is necessary information for using it effectively.
TIMESTAMPTZ arguments are bucketed by the time at UTC. So the alignment of buckets is on UTC time. One consequence of this is that daily buckets are aligned to midnight UTC, not local time.
If the user wants buckets aligned by local time, the TIMESTAMPTZ input should be cast to TIMESTAMP (such a cast converts the value to local time) before being passed to time_bucket (see example below). Note that along daylight savings time boundaries the amount of data aggregated into a bucket after such a cast is irregular: for example if the bucket_width is 2 hours, the number of UTC hours bucketed by local time on daylight savings time boundaries can be either 3 hours or 1 hour.
|INTERVAL||A PostgreSQL time interval for how long each bucket is|
|TIMESTAMP||The timestamp to bucket|
|INTERVAL||The time interval to offset all buckets by|
|TIMESTAMP||Buckets are aligned relative to this timestamp|
|INTEGER||The bucket width|
|INTEGER||The timestamp to bucket|
|INTEGER||The amount to offset all buckets by|
Simple 5-minute averaging:
SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;
To report the middle of the bucket, instead of the left edge:
SELECT time_bucket('5 minutes', time) + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;
For rounding, move the alignment so that the middle of the bucket is at the 5 minute mark (and report the middle of the bucket):
SELECT time_bucket('5 minutes', time, '-2.5 minutes') + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;
To shift the alignment of the buckets you can use the origin parameter (passed as a timestamp, timestamptz, or date type). In this example, we shift the start of the week to a Sunday (the default is a Monday).
SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31') AS one_week, avg(cpu) FROM metrics GROUP BY one_week WHERE time > TIMESTAMPTZ '2017-12-01' AND time < TIMESTAMPTZ '2018-01-03' ORDER BY one_week DESC LIMIT 10;
The value of the origin parameter we used in this example was
2017-12-31, a Sunday within the
period being analyzed. However, the origin provided to the function can be before, during, or
after the data being analyzed. All buckets are calculated relative to this origin. So, in this example,
any Sunday could have been used. Note that because
time < TIMESTAMPTZ '2018-01-03' in this example,
the last bucket would have only 4 days of data.
Bucketing a TIMESTAMPTZ at local time instead of UTC(see note above):
SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;
Note that the above cast to TIMESTAMP converts the time to local time according to the server's timezone setting.