Document that `date_bin` with an explicit origin drifts an hour across a DST transition
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
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.
```sql
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:
```sql
-- 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(, to_local_time(t AT TIME 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).
Contributor guide
Research direction
Start at the date_bin documentation entry point and reproduce the supplied DataFusion 55.0.0 query across the America/Denver DST transition. Document that an explicit origin fixes an instant and can drift from local midnight, then point readers to date_trunc for calendar units and the stated to_local_time pattern for non-calendar strides.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases, documentation
- Issue type
- Documentation
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 74/100