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: [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]
