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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
