Repository navigation
Document that date_bin with an explicit origin drifts an hour across a DST transition #25168
Description
Describe the bug
Passing date_bin an explicit origin chosen to align bins to local midnight works until the timezone's offset changes, then silently shifts by an hour. There is no error and no warning.
This is not a DataFusion bug. PostgreSQL 15.19 and DuckDB 1.5.2 return identical results (see below). All three engines bin on the instant from a fixed origin, so the drift is inherent. What is missing is documentation: the pattern looks correct in every test that does not cross a DST transition.
CREATE OR REPLACE VIEW v AS SELECT arrow_cast(c, 'Timestamp(Second, Some("America/Denver"))') AS t
FROM (VALUES (TIMESTAMP '2024-03-09 06:30:00'), (TIMESTAMP '2024-03-11 06:30:00')) s(c);
SELECT t,
date_bin(INTERVAL '1 day', t) AS no_origin,
date_bin(INTERVAL '1 day', t,
arrow_cast(TIMESTAMP '2024-03-01 07:00:00','Timestamp(Second, Some("UTC"))')) AS with_origin
FROM v;
+---------------------------+---------------------------+---------------------------+
| t | no_origin | with_origin |
+---------------------------+---------------------------+---------------------------+
| 2024-03-09T06:30:00-07:00 | 2024-03-08T17:00:00-07:00 | 2024-03-09T00:00:00-07:00 |
| 2024-03-11T06:30:00-06:00 | 2024-03-10T18:00:00-06:00 | 2024-03-11T01:00:00-06:00 |
+---------------------------+---------------------------+---------------------------+The origin 2024-03-01 07:00:00Z is 2024-03-01 00:00 MST, so bins land on local midnight — until America/Denver springs forward on 2024-03-10. After that the same expression returns 01:00:00-06:00.
To Reproduce
The query above, on DataFusion 55.0.0 (da89c7c85b).
Other engines
Measured with TimeZone = 'America/Denver' for the same instants and origin:
-- PostgreSQL 15.19
SELECT t, date_bin('1 day', t, '2024-03-01 07:00:00+00'::timestamptz), date_trunc('day', t)
FROM (VALUES ('2024-03-09 13:30:00+00'::timestamptz), ('2024-03-11 12:30:00+00'::timestamptz)) v(t);
-- DuckDB 1.5.2 has no date_bin; time_bucket with an origin is the equivalent
SELECT t, time_bucket(INTERVAL '1 day', t, TIMESTAMPTZ '2024-03-01 07:00:00+00'), date_trunc('day', t)
FROM (VALUES (TIMESTAMPTZ '2024-03-09 13:30:00+00'), (TIMESTAMPTZ '2024-03-11 12:30:00+00')) v(t);t |
with origin | date_trunc('day', t) |
|---|---|---|
2024-03-09 06:30:00-07 |
2024-03-09 00:00:00-07 |
2024-03-09 00:00:00-07 |
2024-03-11 06:30:00-06 |
2024-03-11 01:00:00-06 |
2024-03-11 00:00:00-06 |
Both match DataFusion's output exactly.
Expected behavior
Document it on date_bin: an origin fixes an instant, so it does not keep bins on local midnight across a DST transition. Point users at date_trunc for calendar units, and at date_bin(<stride>, to_local_time(t AT TIME ZONE '<zone>')) for strides that are not calendar units. Changing the behaviour would move DataFusion away from PostgreSQL and DuckDB.
Related: #10602 (local-calendar binning) and #25167 (date_bin and date_trunc disagree on timezone-aware input).
Activity
test: pin the date_bin explicit-origin DST drift
adriangb commentedon Sep 11, 2026
Folded into the issue description: PostgreSQL and DuckDB behave identically, so this is now a documentation issue rather than a bug.
[-]`date_bin` with an explicit origin silently drifts an hour across a DST transition[/-][+]Document that `date_bin` with an explicit origin drifts an hour across a DST transition[/+]test: pin the date_bin explicit-origin DST drift
test: pin the date_bin explicit-origin DST drift
test: characterization suite for timestamps with time zone (apache#25175
test: characterization suite for timestamps with time zone (apache#25175
test: characterization suite for timestamps with time zone (apache#25175