You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "adriangb (via GitHub)" <gi...@apache.org> on 2026/09/10 18:13:22 UTC
[I] `date_bin` with an explicit origin silently drifts an hour across a DST transition [datafusion]
adriangb opened a new issue, #25168:
URL: https://github.com/apache/datafusion/issues/25168
### 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.
```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`).
### Expected behavior
Arguably this is inherent: `date_bin` steps by a fixed number of nanoseconds from a fixed instant, so a fixed origin cannot track a variable-length local day. If so, the bug is that it looks like it works. Aligning bins to local midnight with an origin is a natural thing to try, it produces correct results for every test that does not cross a transition, and it then fails quietly in production twice a year.
Either `date_bin` should reject or warn about this usage, or the documentation should say clearly that an origin does not survive a DST transition and point at the `to_local_time` idiom instead.
This is closely tied to #10602 and to the `date_bin`/`date_trunc` inconsistency filed alongside this.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] `date_bin` with an explicit origin silently drifts an hour across a DST transition [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #25168:
URL: https://github.com/apache/datafusion/issues/25168#issuecomment-5636532759
Correction to my own report, after measuring two other engines: **this is not a DataFusion divergence.**
PostgreSQL 15.19 and DuckDB 1.5.2 give identical results for the same instants and origin, with `TimeZone = 'America/Denver'`:
```sql
-- PostgreSQL
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 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` |
That is exactly DataFusion's output. All three engines bin on the instant from a fixed origin, so an origin chosen to hit local midnight drifts by an hour after a DST transition in all of them. The "arguably this is inherent" reading in the description is the correct one.
What remains is only the usability point: the pattern looks correct until the first transition. That is a documentation question — pointing users at `date_trunc`, or at `date_bin(..., to_local_time(t AT TIME ZONE ...))` for non-calendar strides — rather than a correctness bug, so the `bug` label overstates it. I will leave the label, and whether this stays open, to maintainers.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org