time_bucket function

Applies to: Databricks SQL Databricks Runtime 19 and above

Returns the start of the fixed-width time bucket that a timestamp falls into, aligned to an origin.

Syntax

time_bucket(bucketSize, ts [, origin])

Arguments

• bucketSize: An INTERVAL constant expression that specifies the size of each bucket. Supports day-time and year-month intervals. Must be positive and non-zero.
• ts: A TIMESTAMP or TIMESTAMP_NTZ expression to bucket.
• origin: An optional TIMESTAMP or TIMESTAMP_NTZ constant expression that defines the alignment anchor of the bucket grid. Defaults to 1970-01-01 00:00:00. Must be the same type as ts.

Returns

A value of the same type as ts (TIMESTAMP or TIMESTAMP_NTZ). The value is the start of the half-open bucket [start, start + bucketSize) that contains ts. The start is always less than or equal to ts, and the next bucket boundary (start + bucketSize) is strictly greater than ts.

Notes

• time_bucket divides the time axis into consecutive, non-overlapping intervals of size bucketSize, anchored at origin. It returns the start of the interval that contains ts.
• origin does not need to precede ts. It only defines the alignment of the bucket grid. The grid extends infinitely in both directions from origin.
• For TIMESTAMP_NTZ, time_bucket buckets in UTC. For TIMESTAMP, year-month interval buckets and the calendar-day components of day-time interval buckets align to the session time zone.
• If any argument is NULL, the result is NULL.
• Use date_trunc for calendar-aligned boundaries such as grouping by calendar month. Use time_bucket when the bucket width or alignment is user-defined.

Common error conditions

• DATATYPE_MISMATCH.NON_FOLDABLE_INPUT
• DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE
• DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE

Examples

SQL

-- 15-minute buckets (default origin at epoch)
> SELECT time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-01 11:27:00');
 2024-01-01 11:15:00

-- 1-hour buckets
> SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00');
 2024-01-01 11:00:00

-- Custom origin shifts bucket alignment to :05 past the hour
> SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00', TIMESTAMP '1970-01-01 00:05:00');
 2024-01-01 11:05:00

-- TIMESTAMP_NTZ variant
> SELECT time_bucket(INTERVAL '15' MINUTE, TIMESTAMP_NTZ '2024-01-01 11:27:00');
 2024-01-01 11:15:00

-- ts exactly on a bucket boundary returns itself
> SELECT time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-01 11:15:00');
 2024-01-01 11:15:00

-- Origin after ts: grid extends backward
> SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00', TIMESTAMP '2025-01-01 00:30:00');
 2024-01-01 10:30:00

-- Monthly buckets (default epoch origin: 1st of month)
> SELECT time_bucket(INTERVAL '1' MONTH, TIMESTAMP '2024-03-15 11:27:00');
 2024-03-01 00:00:00

-- Quarterly buckets
> SELECT time_bucket(INTERVAL '3' MONTH, TIMESTAMP '2024-05-15 10:00:00');
 2024-04-01 00:00:00

-- Monthly buckets with origin on the 15th
> SELECT time_bucket(INTERVAL '1' MONTH, TIMESTAMP '2024-03-20 09:00:00', TIMESTAMP '1970-01-15 00:00:00');
 2024-03-15 00:00:00

-- Origin on 31st: day clamping in short months
> SELECT time_bucket(INTERVAL '1' MONTH, TIMESTAMP '2024-03-01 12:00:00', TIMESTAMP '1970-01-31 00:00:00');
 2024-02-29 00:00:00

-- Zero bucket width is rejected
> SELECT time_bucket(INTERVAL '0' SECOND, TIMESTAMP '2024-01-01 11:00:00');
 [DATATYPE_MISMATCH.VALUE_OUT_OF_RANGE] The bucketSize must be between (0, inf) (current value = INTERVAL '00' SECOND).

Related functions

• date_trunc function
• TIMESTAMP type
• TIMESTAMP_NTZ type