You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "adriangb (via GitHub)" <gi...@apache.org> on 2026/09/08 15:25:24 UTC
[I] Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries [datafusion]
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #25084:
URL: https://github.com/apache/datafusion/issues/25084#issuecomment-5603355254
The fix is now in arrow-rs: https://github.com/apache/arrow-rs/pull/11038 (issue https://github.com/apache/arrow-rs/issues/11037). The DataFusion-side workaround in https://github.com/apache/datafusion/pull/25115 is closed in favour of it; this issue will be resolved once DataFusion picks up an arrow release containing that change.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #25084:
URL: https://github.com/apache/datafusion/issues/25084#issuecomment-5623270357
Update: this needs **two** upstream fixes, not one. I had assumed https://github.com/apache/arrow-rs/pull/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):
```sql
-- 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 https://github.com/apache/arrow-rs/issues/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 https://github.com/apache/arrow-rs/issues/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 https://github.com/apache/datafusion/pull/25164.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on issue #25084:
URL: https://github.com/apache/datafusion/issues/25084#issuecomment-5587773873
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 @ https://github.com/apache/datafusion/blob/c124860e94edd8e43a7641a354e0724a161b740b/datafusion/functions/src/datetime/parser.rs#L108
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Cast from `Timestamp(_, None)` to a named timezone errors on DST boundaries [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on issue #25084:
URL: https://github.com/apache/datafusion/issues/25084#issuecomment-5623677579
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 https://github.com/apache/arrow-rs/pull/11054, stacked on https://github.com/apache/arrow-rs/pull/11038. Together they close this issue; #11038 alone does not, for the reasons in my previous comment.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org