`AT TIME ZONE '+05:30'` uses the opposite sign convention from PostgreSQL

[adriangb]

Describe the bug

AT TIME ZONE with a fixed-offset string uses the opposite sign convention from PostgreSQL.

DataFusion 55.0.0:

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:

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.