apache / apache/datafusion

Document that `date_bin` with an explicit origin drifts an hour across a DST transition

Open Beginner friendly
#25,168 1 comment 0 reactions 0 assignees View on GitHub
documentation
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.