Dates & time Scalar

time_bucket()

Truncate TIMESTAMPTZ by the specified interval bucket_width. Buckets are aligned relative to origin TIMESTAMPTZ. The origin defaults to 2000-01-03 00:00:00+00 for buckets that do not include a month or year interval, and to 2000-01-01 00:00:00+00 for month and year buckets

Reference: DuckDB 2.0.0

Arguments & returns

13 overloads

time_bucket(bucket_width, timestamp)

Scalar
Overload link bucket_widthtimestamp Returns
#01 INTERVALDATE DATE
#02 INTERVALTIME TIME
#03 INTERVALTIMESTAMP TIMESTAMP
#04 INTERVALTIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE

time_bucket(bucket_width, timestamp, offset)

Scalar
Overload link bucket_widthtimestampoffset Returns
#05 INTERVALDATEINTERVAL DATE
#06 INTERVALTIMEINTERVAL TIME
#07 INTERVALTIMESTAMP WITH TIME ZONEINTERVAL TIMESTAMP WITH TIME ZONE
#08 INTERVALTIMESTAMPINTERVAL TIMESTAMP

time_bucket(bucket_width, timestamp, origin)

Scalar
Overload link bucket_widthtimestamporigin Returns
#09 INTERVALDATEDATE DATE
#10 INTERVALTIMETIME TIME
#11 INTERVALTIMESTAMP WITH TIME ZONETIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE
#12 INTERVALTIMESTAMPTIMESTAMP TIMESTAMP

time_bucket(bucket_width, timestamp, timezone)

Scalar
Overload link bucket_widthtimestamptimezone Returns
#13 INTERVALTIMESTAMP WITH TIME ZONEVARCHAR TIMESTAMP WITH TIME ZONE

Example

time_bucket example
SQL
Haybarn WASM 2.0.0-rc12 Β· In your browser

Engine example Β· Run to view results.

In other engines

PostgreSQL

SELECT DATE_BIN('30 days', timestamp_col, (SELECT MIN(TIMESTAMP) from table)) FROM table
In DuckDB
SELECT TIME_BUCKET('30 days', timestamp_col, (SELECT MIN(TIMESTAMP) FROM "table")) FROM "table"

SQLGlot example β†—

Snowflaketime_slice()

SELECT TIME_SLICE(DATE '2024-03-15', 1, 'DAY')
In DuckDB
SELECT TIME_BUCKET(INTERVAL 1 DAY, CAST('2024-03-15' AS DATE))

SQLGlot example β†—