adriangb opened a new issue, #25084: URL: https://github.com/apache/datafusion/issues/25084
### 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 ```rust 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 https://github.com/apache/datafusion/issues/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: ```sql 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: ```sql 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** ```sql 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** ```sql 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 https://github.com/apache/datafusion/issues/13212 will surface it. Versions: `datafusion-cli` 54.0.0, arrow-cast 59.2.0, PostgreSQL 17, DuckDB 1.5.2. -- 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]
