adriangb commented on issue #25084:
URL: https://github.com/apache/datafusion/issues/25084#issuecomment-5623677579
One more data point on the resolution policy, since this issue is where the
DataFusion-side decision will be judged.
The upstream fixes resolve an ambiguous local time to the **later** instant
and shift a nonexistent one **forward**. That is the only genuinely arbitrary
choice in the change, so I measured a second engine rather than relying on
PostgreSQL alone. DuckDB 1.5.2 (ICU) agrees with PostgreSQL 17.11 on every case:
| Case | PostgreSQL 17.11 | DuckDB 1.5.2 |
| --- | --- | --- |
| `America/New_York` `2024-11-03 01:30` (ambiguous) | `2024-11-03
06:30:00+00` | `2024-11-03 06:30:00+00` |
| `America/New_York` `2024-03-10 02:30` (gap) | `2024-03-10 07:30:00+00` |
`2024-03-10 07:30:00+00` |
| `America/Sao_Paulo` `2018-11-04 00:00` (local midnight does not exist) |
`2018-11-04 03:00:00+00` | `2018-11-04 03:00:00+00` |
| `America/Havana` `2024-11-03 00:00` (local midnight happens twice) |
`2024-11-03 05:00:00+00` | `2024-11-03 05:00:00+00` |
| `Australia/Lord_Howe` `2024-10-06 02:15` (30-minute DST step) |
`2024-10-05 15:45:00+00` | `2024-10-05 15:45:00+00` |
| `Pacific/Chatham` `2024-09-29 03:00` (+12:45/+13:45) | `2024-09-28
14:15:00+00` | `2024-09-28 14:15:00+00` |
| `Australia/Sydney` `2024-10-06 02:30` (southern-hemisphere gap) |
`2024-10-05 16:30:00+00` | `2024-10-05 16:30:00+00` |
The two ambiguous rows are the discriminating ones. `2024-11-03 01:30` in
New York is either `05:30Z` (EDT) or `06:30Z` (EST); both engines give the
later. Havana's ambiguous midnight is either `04:00Z` or `05:00Z`; both give
the later. So PostgreSQL, DuckDB and the arrow-rs fixes all agree on the choice
that had no obvious right answer, and DataFusion inheriting it is not adopting
one engine's quirk.
Both spellings agree too, which is the point of needing both arrow-rs
changes:
```
DuckDB, SET TimeZone='America/New_York'; '2024-03-10 02:30:00'::timestamptz
-> 2024-03-10 03:30:00-04 (= 07:30Z)
DuckDB, SET TimeZone='UTC'; '2024-03-10 02:30:00
America/New_York'::timestamptz
-> 2024-03-10 07:30:00+00
```
The parser-side fix is now open as
https://github.com/apache/arrow-rs/pull/11054, stacked on
https://github.com/apache/arrow-rs/pull/11038. Together they close this issue;
#11038 alone does not, for the reasons in my previous comment.
--
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]
