Group timestamps into custom intervals with the time_bucket function

Use cases

Imagine you need to analyze sensor data every 15 minutes or aggregate financial metrics using a custom time anchor that does not match the start of the calendar month.

• Create custom time intervals such as 15 minutes, 1 hour, or 3 months.
• Define an arbitrary time anchor for grid alignment.
• Replace date_trunc when the interval width is not a standard calendar unit.

How to use

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

What happens in this code:

1. The function takes the 15-minute interval and the input timestamp.
2. It calculates the start of the half-open bucket containing the timestamp.
3. It returns the timestamp aligned to the lower bound of the bucket.

SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00', TIMESTAMP '1970-01-01 00:05:00');

What happens in this code:

1. The function sets the interval size to 1 hour.
2. It applies a custom origin to shift the bucket alignment by 5 minutes.
3. It returns the start of the bucket adjusted to the new anchor.

What it returns

Field	Description
TIMESTAMP or TIMESTAMP_NTZ	The start of the fixed-width time interval containing the input timestamp.

Limitations and requirements

Available in Databricks SQL and Databricks Runtime 19 and above. The bucketSize parameter must be a positive, non-zero interval. The optional origin argument must match the type of the input timestamp.

Using it in a pipeline

Use this function in SQL queries within the Silver or Gold layer to standardize timestamps into custom time windows before performing aggregations and time-series analyses.