adriangb opened a new issue, #25170:
URL: https://github.com/apache/datafusion/issues/25170
### Describe the bug
`AT TIME ZONE` with a fixed-offset *string* uses the opposite sign
convention from PostgreSQL.
DataFusion 55.0.0:
```sql
SELECT arrow_cast(TIMESTAMP '2024-01-01 12:00:00','Timestamp(Second,
Some("UTC"))')
AT TIME ZONE '+05:30';
-- 2024-01-01T17:30:00+05:30
```
PostgreSQL 17.11:
```sql
SET TimeZone='UTC';
SELECT '2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE '+05:30' AS
as_string,
'2024-01-01T12:00:00Z'::timestamptz AT TIME ZONE INTERVAL '05:30' AS
as_interval;
as_string | as_interval
---------------------+---------------------
2024-01-01 06:30:00 | 2024-01-01 17:30:00
```
PostgreSQL treats the *string* `'+05:30'` as POSIX-style, where the sign is
west-positive, giving `06:30`. It treats the *interval* `INTERVAL '05:30'` as
east-positive, giving `17:30`. DataFusion's string form matches PostgreSQL's
interval form, i.e. it uses the ISO/RFC-3339 convention that Arrow uses
everywhere else.
DuckDB 1.5.2 rejects `'+05:30'` in `AT TIME ZONE` outright rather than
picking a convention.
### To Reproduce
The queries above.
### Expected behavior
Unclear, deliberately. DataFusion's answer is arguably the better one — it
is self-consistent with how `Timestamp(_, Some("+05:30"))` is interpreted
everywhere else in Arrow, and PostgreSQL's own string/interval split is a
well-known wart. But it means a query copied from PostgreSQL silently returns
an instant 11 hours away, with no error.
Filing it so the divergence is on record and the choice is deliberate.
Reasonable outcomes: keep the behaviour and document it, or reject bare
fixed-offset strings in `AT TIME ZONE` as DuckDB does and require `INTERVAL`.
This is pre-existing and orthogonal to #25165, which changes only the result
*type* of `AT TIME ZONE` on an already-aware value.
--
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]
