time_bucket_ng() function is an experimental version of the
time_bucket() function. It introduced some new capabilities,
such as monthly buckets and timezone support. Those features are now part of the
This section describes a feature that is deprecated on Timescale. We strongly recommend that you do not use this feature in a production environment. If you need more information, contact us.
time_bucket_ng() functions are similar, but not completely compatible. There are two main differences.
time_bucket_ng() doesn't work with timestamps prior to
Secondly, the default
origin values differ.
time_bucket() uses an origin
date of January 3, 2000, for buckets shorter than a month.
uses an origin date of January 1, 2000, for all bucket sizes.
|INTERVAL||A PostgreSQL time interval for how long each bucket is|
|DATE, TIMESTAMP or TIMESTAMPTZ||The timestamp to bucket|
|Should be the same as ||Buckets are aligned relative to this timestamp|
|TEXT||The name of the timezone. The argument can be specified only if the type of |
For backward compatibility with
timezone argument is
optional. However, it is required for time buckets that are less than 24 hours.
If you call the TIMESTAMPTZ-version of the function without the
argument, the timezone defaults to the session's timezone and so the function
can't be used with continuous aggregates. Best practice is to use
time_bucket_ng(interval, timestamptz, text) and specify the timezone.
The function returns the bucket's start time. The return value type is the
In this example,
time_bucket_ng() is used to create bucket data in three month
SELECT timescaledb_experimental.time_bucket_ng('3 month', date '2021-08-01');time_bucket_ng----------------2021-07-01(1 row)
This example uses
time_bucket_ng() to bucket data in one year intervals:
SELECT timescaledb_experimental.time_bucket_ng('1 year', date '2021-08-01');time_bucket_ng----------------2021-01-01(1 row)
To split time into buckets,
time_bucket_ng() uses a starting point in time
origin. The default origin is
time_bucket_ng cannot use
timestamps earlier than
SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08');ERROR: origin must be before the given date
Going back in time from
origin isn't usually possible, especially when you
consider timezones and daylight savings time (DST). Note also that there is no
reasonable way to split time in variable-sized buckets (such as months) from an
origin defaults to the first day of the month.
To bypass named limitations, you can override the default
-- working with timestamps before 2000-01-01SELECT timescaledb_experimental.time_bucket_ng('100 years', timestamp '1988-05-08', origin => '1900-01-01');time_bucket_ng---------------------1900-01-01 00:00:00-- unlike the default origin, which is Saturday, 2000-01-03 is MondaySELECT timescaledb_experimental.time_bucket_ng('1 week', timestamp '2021-08-26', origin => '2000-01-03');time_bucket_ng---------------------2021-08-23 00:00:00
This example shows how
time_bucket_ng() is used to bucket data
by months in a specified timezone:
-- note that timestamptz is displayed differently depending on the session parametersSET TIME ZONE 'Europe/Moscow';SETSELECT timescaledb_experimental.time_bucket_ng('1 month', timestamptz '2001-02-03 12:34:56 MSK', timezone => 'Europe/Moscow');time_bucket_ng------------------------2001-02-01 00:00:00+03
You can use
time_bucket_ng() with continuous aggregates. This example tracks
the temperature in Moscow over seven day intervals:
CREATE TABLE conditions(day DATE NOT NULL,city text NOT NULL,temperature INT NOT NULL);SELECT create_hypertable('conditions', 'day',chunk_time_interval => INTERVAL '1 day');INSERT INTO conditions (day, city, temperature) VALUES('2021-06-14', 'Moscow', 26),('2021-06-15', 'Moscow', 22),('2021-06-16', 'Moscow', 24),('2021-06-17', 'Moscow', 24),('2021-06-18', 'Moscow', 27),('2021-06-19', 'Moscow', 28),('2021-06-20', 'Moscow', 30),('2021-06-21', 'Moscow', 31),('2021-06-22', 'Moscow', 34),('2021-06-23', 'Moscow', 34),('2021-06-24', 'Moscow', 34),('2021-06-25', 'Moscow', 32),('2021-06-26', 'Moscow', 32),('2021-06-27', 'Moscow', 31);CREATE MATERIALIZED VIEW conditions_summary_weeklyWITH (timescaledb.continuous) ASSELECT city,timescaledb_experimental.time_bucket_ng('7 days', day) AS bucket,MIN(temperature),MAX(temperature)FROM conditionsGROUP BY city, bucket;SELECT to_char(bucket, 'YYYY-MM-DD'), city, min, maxFROM conditions_summary_weeklyORDER BY bucket;to_char | city | min | max------------+--------+-----+-----2021-06-12 | Moscow | 22 | 272021-06-19 | Moscow | 28 | 342021-06-26 | Moscow | 31 | 32(3 rows)
For more information, see the continuous aggregates documentation.
time_bucket_ng() supports months and timezones, continuous aggregates cannot always be used with monthly buckets or buckets with timezones.
This table shows which
time_bucket_ng() functions can be used in a continuous aggregate:
|Function||Available in continuous aggregate||TimescaleDB version|
|Buckets by seconds, minutes, hours, days, and weeks||✅||2.4.0 and later|
|Buckets by months and years||✅||2.6.0 or later|
|Timezones support||✅||2.6.0 or later|
|Specify custom origin||✅||2.7.0 or later|
Found an issue on this page?Report an issue!