time_bucket function | Databricks on AWS
The time\_bucket function in Databricks SQL and Databricks Runtime returns the start of a fixed-width time bucket for a timestamp, aligned to an origin.
examples:
-- 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).