time_bucket

Function

time_bucket is a time range alignment function. It is used for aligning a time based on bucket_width for aggregating time later.

Precautions

When time_bucket is used, because offset and origin are mutually exclusive, the third parameter of time_bucket may be offset (interval) or origin (timestamp). To distinguish offset and origin easily, using offset or origin needs to specify the third parameter, such as:

select time_bucket('5minute',time,'2001-1-1 00:00:00'::timestamp) as bucket ,count(temperature) from weather group by bucket order by bucket asc;

select time_bucket('1minute',time,'1minute'::interval) as bucket ,count(temperature) from weather group by bucket order by bucket asc;

Syntax Format

time_bucket(bucket_width, time,[offset | origin])

Parameter Description

Parameter Name	Type	Mandatory or Not	Description
bucket_width	TEXT	Yes	Time interval in the Positive integer + Time mark format. The time mark includes all units supported by the interval type, including microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium, the plurality of these units, such as years, months, and days, and the abbreviated format of these units, such as y, m, and d.
time	TIMESTAMP	Yes	Time to be aligned.
offset	INTERVAL	No	Adjusts the time window position. If the value is a positive, the time window moves forward. Otherwise, the time window moves backward.
origin	TIMESTAMP	No	Base time. By default, the time is aligned based on 2001-1-1 00:00:00. This parameter is mutually exclusive with offset.

Examples

Uqbar=#  select * from t1 order by time;
          time          | id | value 
------------------------+----+-------
 2022-06-02 00:01:00+08 |  1 |     9
 2022-06-02 00:04:00+08 |  1 |    11
 2022-06-02 00:06:00+08 |  1 |    10
 2022-06-02 00:07:00+08 |  1 |    11
 2022-06-02 00:15:00+08 |  1 |    12
(5 rows)

Uqbar=# select time_bucket('2 minute',time) as bucket, sum(value)  from t1 group by bucket order by bucket; 
          bucket         | sum 
------------------------------------+-----
 2022-06-02 00:00:00+08   |   9
 2022-06-02 00:04:00+08   |  11
 2022-06-02 00:06:00+08   |  21
 2022-06-02 00:14:00+08   |  12
(4 rows)