Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries

[adriangb]

Describe the bug

Casting Timestamp(_, None) to a timestamp with a named timezone fails with an error whenever the naive local time falls on a DST boundary — both the ambiguous "fall back" hour and the nonexistent "spring forward" hour:

Arrow error: Cast error: Cannot cast timezone to different timezone

Unambiguous local times cast fine, and fixed-offset timezones (+08:00) are never affected because they have no DST transitions.

The root cause is in arrow-rs, in adjust_timestamp_to_timezone:

https://github.com/apache/arrow-rs/blob/59.2.0/arrow-cast/src/cast/mod.rs#L2585-L2605

let adjust = |o| {
    let local = as_datetime::<T>(o)?;
    let offset = to_tz.offset_from_local_datetime(&local).single()?;
    T::from_naive_datetime(local - offset.fix(), None)
};

.single() returns None for both LocalResult::Ambiguous and LocalResult::None, which becomes the cast error above (or a silent NULL under CastOptions { safe: true }).

This matters for #13212: the fix there will make DataFusion insert exactly this cast during type coercion, so any query mixing Timestamp(_, None) with a timezone-aware timestamp under a named session timezone will start hitting this on DST-boundary values.

To Reproduce

datafusion-cli 54.0.0:

SET datafusion.execution.time_zone = 'America/New_York';

-- unambiguous: works
SELECT '2024-11-01T00:00:00'::timestamp::timestamptz;
+---------------------------+
| 2024-11-01T00:00:00-04:00 |
+---------------------------+

-- ambiguous (DST fall-back, 01:30 occurs twice): error
SELECT '2024-11-03T01:30:00'::timestamp::timestamptz;
Arrow error: Cast error: Cannot cast timezone to different timezone

-- nonexistent (DST spring-forward gap, 02:30 does not exist): error
SELECT '2024-03-10T02:30:00'::timestamp::timestamptz;
Arrow error: Cast error: Cannot cast timezone to different timezone

Not a constant-folding artifact — it reproduces on a real column too:

SET datafusion.execution.time_zone = 'America/New_York';
CREATE TABLE t AS SELECT arrow_cast('2024-11-03T01:30:00', 'Timestamp(Nanosecond, None)') AS ts;
SELECT ts::timestamptz FROM t;
Arrow error: Cast error: Cannot cast timezone to different timezone

Expected behavior

Both PostgreSQL and DuckDB resolve these deterministically rather than erroring, and they agree with each other exactly.

PostgreSQL 17

SET TimeZone='America/New_York';

SELECT '2024-11-03T01:30:00'::timestamp::timestamptz;
--> 2024-11-03 01:30:00-05        (ambiguous: picks the later/standard offset)

SELECT '2024-03-10T02:30:00'::timestamp::timestamptz;
--> 2024-03-10 03:30:00-04        (gap: shifted forward)

DuckDB 1.5.2

SET TimeZone='America/New_York';

SELECT '2024-11-03T01:30:00'::timestamp::timestamptz;
--> 2024-11-03 01:30:00-05

SELECT '2024-03-10T02:30:00'::timestamp::timestamptz;
--> 2024-03-10 03:30:00-04

So the expected convention is:

• Ambiguous (repeated hour): choose the later offset — i.e. standard time, the second occurrence.
• Nonexistent (gap hour): shift forward by the size of the gap.

SQLite has no timezone-aware timestamp type and no session timezone, so it offers no reference behavior here.

Additional context

Fixing this most likely requires a change in arrow-rs (adjust_timestamp_to_timezone needs to handle LocalResult::Ambiguous and LocalResult::None instead of collapsing them via .single()), possibly exposed through CastOptions so callers can pick a policy. Filing here first since DataFusion is where the behavior is observed and where #13212 will surface it.

Versions: datafusion-cli 54.0.0, arrow-cast 59.2.0, PostgreSQL 17, DuckDB 1.5.2.

[Omega359]

I vaguely recall encountering a similar issue with named timezones with formatting timestamps, I believe I had a solution in my abandoned jiff PR. source code @

datafusion/datafusion/functions/src/datetime/parser.rs

Line 108 in c124860

fn string_to_datetime_formatted<T: chrono::TimeZone>(

[adriangb]

The fix is now in arrow-rs: apache/arrow-rs#11038 (issue apache/arrow-rs#11037). The DataFusion-side workaround in #25115 is closed in favour of it; this issue will be resolved once DataFusion picks up an arrow release containing that change.

[adriangb]

Update: this needs two upstream fixes, not one. I had assumed apache/arrow-rs#11038 would close it. It does not.

There are two independent code paths here, and they fail with two different errors. Verified on DataFusion da89c7c85b (55.0.0):

-- path 1: the timestamp cast kernel  -> arrow-cast/src/cast/mod.rs
SELECT '2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/New_York';
-- Arrow error: Cast error: Cannot cast timezone to different timezone

-- path 2: the string parser  -> arrow-cast/src/parse.rs
SELECT '2024-03-10 02:30:00' AT TIME ZONE 'America/New_York';
-- Arrow error: Parser error: Error parsing timestamp from '2024-03-10 02:30:00': error computing timezone offset

SET datafusion.execution.time_zone = 'America/New_York';
SELECT '2024-03-10 02:30:00'::timestamptz;
-- Arrow error: Parser error: Error parsing timestamp from '2024-03-10 02:30:00': error computing timezone offset

To a user those are the same query written three ways. Only the first is fixed by #11038; the other two go through string_to_datetime, which still resolves the local time with LocalResult::single() in three places. That is apache/arrow-rs#11039.

Note the third form especially — '...'::timestamptz under a named session time zone is the most natural way to write this, and it is entirely on the parser path, so #11038 does not help it at all. Landing #11038 alone would leave the engine self-inconsistent: adding an explicit ::timestamp in the middle of an expression would make it start working.

So apache/arrow-rs#11039 is a hard blocker for this issue, not a follow-up. Both arrow-rs changes plus an arrow version bump are needed before this can be closed.

On the first path, #11038 itself is in good shape. I validated it against 97,162 cases covering every transition of every IANA zone in 1890-1995 and 2023-2025 (15,871 in a gap, 14,370 ambiguous): zero disagreements with an independent implementation on the same tzdata, and agreement with PostgreSQL 17 and DuckDB/ICU wherever their tzdata versions agree. Offsets resolve per row, confirmed on arrays that straddle a transition. No existing DataFusion sqllogictest expectation changes.

One consequence worth knowing before the bump lands: DataFusion has no test coverage for this behaviour today. There is no Cannot cast timezone to different timezone expectation anywhere in test_files/, so nothing in the suite would catch a regression here — including a per-array-instead-of-per-row offset bug. Coverage is being added in #25164.

[adriangb]

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 apache/arrow-rs#11054, stacked on apache/arrow-rs#11038. Together they close this issue; #11038 alone does not, for the reasons in my previous comment.