Bin a timestamp into uniform-width intervals aligned to a chosen origin.
DATE_BIN(stride, source, origin)## Overview Buckets a timestamp into uniform-width intervals, floored to the nearest bin boundary at or before the source value, using a chosen origin for alignment. Unlike DATE_TRUNC (which snaps to fixed calendar units like hour or day), DATE_BIN lets you choose an arbitrary bin width and origin, making it suitable for 5-minute, 15-minute, or custom-offset windows. The result is the greatest timestamp that equals origin plus an integer multiple of stride and is less than or equal to source. ## Behavior - Returns a TIMESTAMP (preserving the input's zone type). - Returns NULL if any argument is NULL. - The stride must be a positive INTERVAL. Negative or zero strides raise an error. - The stride must be expressible in seconds: multiples of months or years are not allowed (since their length is variable). Use DATE_TRUNC for month-level or year-level bucketing. - The origin can be any TIMESTAMP; it does not need to be earlier than source. - The result is always at or before source (floor behavior). ## Timezone handling - For TIMESTAMP WITH TIME ZONE inputs, arithmetic is performed on the underlying UTC instant, so results are zone-stable. - DST transitions do not disturb fixed-second strides (for example INTERVAL '1 hour'): bins remain uniformly 3600 seconds apart in UTC, even though the local wall-clock representation may show a doubled or skipped hour. ## Compatibility - Uniform-width time-series bucketing function. Standard alternative to combining EXTRACT(EPOCH FROM ...) with integer arithmetic.
| Name | Type | Description |
|---|---|---|
stride | Specifies the bin width as an INTERVAL value (for example INTERVAL '15 minutes', INTERVAL '1 hour', INTERVAL '1 day'). | |
source | Specifies the timestamp value to place into a bin. | |
origin | Specifies the alignment origin. Bin boundaries are the origin plus integer multiples of stride. |
-- 15-minute bins aligned to midnight
SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2025-03-15 09:22:00', TIMESTAMP '2025-03-15 00:00:00') AS bin;-- 1-hour bins aligned to the start of the day
SELECT DATE_BIN(INTERVAL '1 hour', TIMESTAMP '2025-03-15 14:37:22', TIMESTAMP '2025-03-15 00:00:00') AS bin;-- Aggregate sensor readings into 5-minute windows
SELECT DATE_BIN(INTERVAL '5 minutes', reading_ts, TIMESTAMP '2025-01-01 00:00:00') AS bucket,
AVG(value) AS avg_value
FROM industrial.iot.sensor_readings
GROUP BY bucket
ORDER BY bucket;-- Shift bin alignment with a custom origin
SELECT DATE_BIN(INTERVAL '30 minutes', TIMESTAMP '2025-03-15 09:10:00', TIMESTAMP '2025-03-15 00:05:00') AS bin;-- Hourly bins for cross-day aggregation
SELECT DATE_BIN(INTERVAL '1 hour', event_ts, TIMESTAMP '2025-01-01 00:00:00') AS hour_bin,
COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY hour_bin
ORDER BY hour_bin;-- 10-minute bins aligned to 3 AM UTC (shifted origin)
SELECT DATE_BIN(INTERVAL '10 minutes', event_ts, TIMESTAMP '2025-01-01 03:00:00') AS bin,
COUNT(*) AS events
FROM telemetry.web.requests
GROUP BY bin
ORDER BY bin;