adriangb commented on issue #13962:
URL: https://github.com/apache/datafusion/issues/13962#issuecomment-5636354698
I can explain this, and there is a workaround today.
The intent of the query is right. The wrong result comes from two DataFusion
behaviours stacking, not from `date_trunc`. I reproduced it on DataFusion
55.0.0 with the column types set explicitly, using three rows at
`2024-01-01T02:00Z`, `2024-01-01T23:00Z` and `2024-01-02T02:00Z`. In New York
those are 2023-12-31 21:00, 2024-01-01 18:00 and 2024-01-01 21:00, so the
correct New York days are `2023-12-31 → 1` and `2024-01-01 → 2`.
### If `timestamp_utc` is timezone-aware (`Timestamp(_, Some("UTC"))`)
```sql
date_trunc('day', (timestamp_utc AT TIME ZONE
'America/New_York')::timestamptz)
```
| | DataFusion 55.0.0 | PostgreSQL 17 |
| --- | --- | --- |
| as reported | `2024-01-01 → 2`, `2024-01-02 → 1` (UTC days) | `2023-12-31
→ 1`, `2024-01-01 → 2` |
| without `::timestamptz` | `2023-12-31 → 1`, `2024-01-01 → 2` | `2023-12-31
→ 1`, `2024-01-01 → 2` |
Two behaviours combine:
1. `AT TIME ZONE` on a timezone-aware value keeps it timezone-aware and only
relabels the display zone. PostgreSQL instead returns a naive timestamp holding
the New York wall clock. That is #12218.
2. `::timestamptz` then replaces the New York label. With
`datafusion.execution.time_zone` unset (the default), it produces a
timezone-naive value holding the **UTC** wall clock. With the session zone set
to `UTC`, it relabels to UTC instead. Either way `date_trunc` then sees UTC
days. That is #25166.
`date_trunc` itself behaves correctly here. It truncates in the value's own
zone, which is why removing `::timestamptz` fixes the result.
**Workaround today:** drop the `::timestamptz`.
```sql
date_trunc('day', timestamp_utc AT TIME ZONE 'America/New_York')
```
### If `timestamp_utc` is naive and holds UTC wall clocks (`Timestamp(_,
None)`)
Then a single `AT TIME ZONE` is the wrong conversion in either engine. It
reads the stored UTC wall clock as if it were New York local time. Convert from
UTC first:
```sql
date_trunc('day', timestamp_utc AT TIME ZONE 'UTC' AT TIME ZONE
'America/New_York')
```
That gives `2023-12-31 → 1`, `2024-01-01 → 2` in both DataFusion 55.0.0 and
PostgreSQL 17.
### Fix
#25165 makes `AT TIME ZONE` on a timezone-aware value return the naive New
York wall clock, as PostgreSQL does. With it applied, the query exactly as
reported returns the correct New York days for a timezone-aware column,
including with the session zone set to `UTC`. #25165 is still under review and
has a known limitation with `CASE` inputs. The `::timestamptz` behaviour itself
is tracked in #25166.
--
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]
