Repository navigation
Commit 2166741
Resolve DST-ambiguous and nonexistent local times when casting timestamps to a named timezone (#11038)
# Which issue does this PR close?
- Closes #11037.
# Rationale for this change
## What a user hits today
Cast a naive timestamp to a named timezone. Pick a wall clock reading
that the zone skips or repeats. The cast fails.
```rust
use arrow_array::TimestampSecondArray;
use arrow_cast::{cast_with_options, CastOptions};
use arrow_schema::{DataType, TimeUnit};
// 1_710_037_800 is 2024-03-10T02:30:00, held as a naive wall clock reading.
// America/New_York skips that hour: the clocks go forward at 02:00.
let array = TimestampSecondArray::from(vec![1_710_037_800]);
let to = DataType::Timestamp(TimeUnit::Second, Some("America/New_York".into()));
let strict = CastOptions { safe: false, ..Default::default() };
let lenient = CastOptions { safe: true, ..Default::default() };
cast_with_options(&array, &to, &strict);
cast_with_options(&array, &to, &lenient);
```
Today:
```text
strict -> Err("Cast error: Cannot cast timezone to different timezone")
lenient -> Ok, null_count = 1
```
After this PR:
```text
strict -> Ok, value = 1710055800 (2024-03-10T07:30:00Z, i.e. 03:30 EDT)
lenient -> Ok, null_count = 0
```
The `safe: true` result is the dangerous one. A whole column of
DST-boundary readings turns to NULL, and nothing reports it.
## How this reaches a SQL user
A DataFusion user reaches the same kernel through ordinary SQL. This is
how the bug was found:
```sql
SELECT '2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/New_York';
-- Cast error: Cannot cast timezone to different timezone
```
The engine plans that as `Timestamp(_, None)` to `Timestamp(_,
Some("America/New_York"))`, so the error text above comes straight from
this kernel. See apache/datafusion#25084. An
upcoming type-coercion change inserts the same cast automatically for
`timestamptz - timestamp`, so the failure gets more common, not less.
## Why the kernel fails
`adjust_timestamp_to_timezone` reads each value as a wall clock time in
the target zone. In an IANA timezone that reading is not always one
instant:
* A "fall back" transition repeats an hour, so the reading is
**ambiguous**.
* A "spring forward" transition skips an hour, so the reading is
**nonexistent**.
The kernel resolved the offset with
`offset_from_local_datetime(..).single()`. That is `None` for both
cases, so both cases fail.
# What changes are included in this PR?
A new private helper, `resolve_local_offset`, decides the two cases
instead of rejecting them. It follows PostgreSQL and DuckDB:
* An **ambiguous** reading takes the **later** instant. This is the
offset in effect after the transition. `2024-11-03T01:30:00` in
`America/New_York` becomes `01:30-05:00` (EST).
* A **nonexistent** reading moves **forward** by the length of the gap.
`2024-03-10T02:30:00` in `America/New_York` becomes `03:30-04:00` (EDT).
`adjust_timestamp_to_timezone` calls the helper in place of `.single()`.
That is the whole behavioural change.
The forward shift needs the offset in effect *before* the transition.
The code recovers it with one probe: it asks for the offset 24 hours
earlier and takes the earliest result. `offset_from_utc_datetime` is not
a valid shortcut, because it returns the post-transition offset. That is
wrong for a southern hemisphere gap such as `Australia/Sydney`
`2024-10-06T02:30:00`.
Two properties of the timezone database make the probe safe:
* No two transitions in any of the 597 zones are closer than **167
hours**. So the probe lands on the correct side of the transition. The
smallest interval is `America/Boa_Vista` in 2000.
* No local gap is longer than **24 hours**. So the probe lands outside
the gap at all. Seven zones sit exactly at 24 hours — the dateline
changes, such as `Pacific/Apia` in 2011. The next largest gap is 10
hours.
If the probe still fails, the helper returns `None` and the caller keeps
the old error or NULL. With current tzdata that branch is unreachable.
Nothing else changes. Unit conversion, `safe` handling and the error
message stay as they were. A fixed-offset timezone has no transitions,
so it is unaffected.
# Are these changes tested?
Yes. Six new tests sit next to the existing
`test_cast_timestamp_with_timezone_*` tests in
`arrow-cast/src/cast/mod.rs`:
* `test_cast_timestamp_to_named_timezone_dst` — `America/New_York`, with
an unambiguous, an ambiguous and a nonexistent reading plus a null,
under `safe: false`.
* `test_cast_timestamp_to_named_timezone_dst_safe` — the same input
under `safe: true`, which no longer produces nulls.
* `test_cast_timestamp_to_named_timezone_dst_southern_hemisphere` —
`Australia/Sydney`, where the transitions run the other way round.
* `test_cast_timestamp_to_named_timezone_dst_nanosecond` and
`..._dst_changing_unit` — the resolution composes with the unit paths.
* `test_cast_timestamp_to_fixed_offset_timezone_unaffected` — `+08:00`
on the same readings is unchanged.
Expected values come from `chrono`, built from the wall clock reading
and the expected offset. They are not hardcoded. Revert the one-line
kernel change and the five DST tests fail, while the fixed-offset test
still passes.
## Validation
The change was checked against 97,162 cases. These cover every
transition of every IANA zone in 1890–1995 and 2023–2025: 15,871 in a
gap and 14,370 ambiguous. There were zero disagreements with an
independent implementation on the same tzdata.
Offsets resolve **per row**. One array that straddles both 2024
`America/New_York` transitions yields three distinct offsets, and every
element matches PostgreSQL 17.11.
One unrelated discrepancy came up. chrono-tz 0.10.4 bundles tzdata
**2025b**, which puts the `Europe/Chisinau` and `Europe/Tiraspol`
transitions an hour away from PostgreSQL, ICU and CPython. That
reproduces on `main` without this PR. It needs a chrono-tz bump, not a
change here.
## A note on the test manifest
arrow-cast had no way to name an IANA zone in its own tests. `Tz` parses
IANA names only when `arrow-array` is built with `chrono-tz`, and
arrow-cast never enabled it.
This PR enables it on the **dev-dependency only**: `arrow-array = {
workspace = true, features = ["chrono-tz"] }` under
`[dev-dependencies]`. The new tests then run in every arrow-cast CI job.
No public feature appears, and nothing changes for downstream crates.
One test needed an update. `test_cast_string_to_timestamp_invalid_tz`
asserted the exact error tail `only offset based timezones supported
without chrono-tz feature`, which is now `failed to parse timezone`. It
asserts the stable prefix `Parser error: Invalid timezone \"ZZTOP\":`
instead.
Local runs that pass: `cargo test -p arrow-cast` (default and
`--all-features`, debug and release), `cargo test -p arrow --features
chrono-tz,prettyprint --test array_cast --test timezone`, clippy with
`-D warnings` on the CI feature combinations, and `cargo doc
--all-features` with `-D warnings`.
# Field research: PostgreSQL 17 and DuckDB
The resolution policy is the one arbitrary decision in this change, so
it needs more than one reference. Both engines were measured directly:
PostgreSQL 17.11 in `postgres:17`, and the DuckDB 1.5.2 CLI with the ICU
extension. All times below are UTC.
| Case | PostgreSQL 17.11 | DuckDB 1.5.2 | This PR |
| --- | --- | --- | --- |
| `America/New_York` `2024-11-03 01:30` — **ambiguous** | `2024-11-03
06:30:00` | `2024-11-03 06:30:00` | `2024-11-03 06:30:00` |
| `America/Havana` `2024-11-03 00:00` — **ambiguous** local midnight |
`2024-11-03 05:00:00` | `2024-11-03 05:00:00` | `2024-11-03 05:00:00` |
| `America/New_York` `2024-03-10 02:30` — gap | `2024-03-10 07:30:00` |
`2024-03-10 07:30:00` | `2024-03-10 07:30:00` |
| `America/Sao_Paulo` `2018-11-04 00:00` — local midnight does not exist
| `2018-11-04 03:00:00` | `2018-11-04 03:00:00` | `2018-11-04 03:00:00`
|
| `Australia/Sydney` `2024-10-06 02:30` — southern hemisphere gap |
`2024-10-05 16:30:00` | `2024-10-05 16:30:00` | `2024-10-05 16:30:00` |
| `Australia/Lord_Howe` `2024-10-06 02:15` — 30 minute DST step |
`2024-10-05 15:45:00` | `2024-10-05 15:45:00` | `2024-10-05 15:45:00` |
| `Pacific/Chatham` `2024-09-29 03:00` — +12:45 / +13:45 | `2024-09-28
14:15:00` | `2024-09-28 14:15:00` | `2024-09-28 14:15:00` |
**The two ambiguous rows carry the argument.** A gap has only one
sensible answer, so it discriminates nothing. An ambiguous reading has
two real answers, and the choice between them is the policy.
`2024-11-03 01:30` in New York is either `05:30Z` (EDT) or `06:30Z`
(EST). Both engines give `06:30Z`. Havana's ambiguous midnight is either
`04:00Z` (CDT) or `05:00Z` (CST). Both engines give `05:00Z`. In both
cases the answer is **the later instant**, which is what this PR does.
## Spark takes the earlier instant
Spark 4.2.0 was measured on the same cases. `CAST(TIMESTAMP_NTZ ... AS
TIMESTAMP)`, `to_utc_timestamp`, `convert_timezone` and a string cast
all agree, with ANSI mode on and off. Spark matches every gap row above.
It takes the **earlier** instant for every ambiguous reading: New York
`01:30` gives `05:30Z`, Havana midnight gives `04:00Z`, and
`Australia/Sydney` `2024-04-07 02:30` gives `2024-04-06 15:30Z`. Spark
follows Java here: the cast calls
[`convertTz`](https://github.com/apache/spark/blob/b1b2685058d6359e00842cbd4c723f4ce3810709/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/Cast.scala#L901),
which uses `LocalDateTime.atZone` (`ZonedDateTime.of`). This PR keeps
the PostgreSQL and DuckDB rule.
## The case neither engine can arbitrate
A fixed-offset string such as `'+05:30'` has no agreed reading, so it is
recorded here rather than hidden. Measured:
| Spelling | PostgreSQL 17.11 | Convention |
| --- | --- | --- |
| `SET TimeZone='+05:30'` then `'2024-01-01 12:00:00'::timestamptz` |
`17:30Z` | POSIX, west-positive |
| `'2024-01-01 12:00:00'::timestamp AT TIME ZONE '+05:30'` | `17:30Z` |
POSIX, west-positive |
| `timestamptz '2024-01-01 12:00:00 +05:30'` | `06:30Z` | ISO,
east-positive |
PostgreSQL agrees with arrow-rs when the offset sits inside the literal.
It disagrees when the offset acts as a zone name. DuckDB 1.5.2 rejects
the zone-name spelling outright: `Not implemented Error: Unknown
TimeZone '+05:30'`.
So neither engine settles it. This PR does not change that behaviour. It
is tracked downstream in
apache/datafusion#25170.
# Are there any user-facing changes?
Yes. A cast that previously raised an error, or returned NULL under
`safe: true`, now returns the instants described above. There is no API
change.
## This PR does not close DataFusion #25084 on its own
There are two independent paths to a DST-boundary failure. This PR fixes
one of them:
```sql
-- cast kernel, fixed here
SELECT '2024-03-10 02:30:00'::timestamp AT TIME ZONE 'America/New_York';
-- string parser, NOT fixed here (see #11039)
SELECT '2024-03-10 02:30:00' AT TIME ZONE 'America/New_York';
SET datafusion.execution.time_zone = 'America/New_York';
SELECT '2024-03-10 02:30:00'::timestamptz;
-- Parser error: Error parsing timestamp from '2024-03-10 02:30:00': error computing timezone offset
```
To a user those are the same query, and the last spelling is the most
natural one. Merge this alone and an explicit `::timestamp` in the
middle of an expression makes a query start to work. That is harder to
explain than the current uniform failure. #11039 must land with this PR
or close behind it, and must use the same policy so the two paths cannot
drift.
This PR **is** sufficient for
apache/datafusion#10308, which is entirely on
the cast path.
## Follow-up: `arrow-array` resolves ambiguity the opposite way
After this PR, arrow resolves an ambiguous local time two different ways
in two crates:
| | Ambiguous | In a gap |
| --- | --- | --- |
| `arrow-cast::resolve_local_offset` (this PR) | the **later** instant |
offset probed 24 hours earlier |
| `arrow-array::types::from_naive_datetime`
([`types.rs:347`](https://github.com/apache/arrow-rs/blob/main/arrow-array/src/types.rs#L347))
| the **earlier** instant (`Ambiguous(dt1, _)`) | `None` |
That function is deliberately untouched here.
This matters downstream. DataFusion calls the `arrow-array` form
directly with a `Some(tz)` in
`datafusion/functions/src/datetime/date_part.rs` (`date_to_scalar`, four
call sites). It builds local-midnight bounds for the `date_part(YEAR,
col) = <year>` rewrite. Where that local midnight is ambiguous, the
earlier instant becomes an **upper** bound, so valid rows can drop out.
The behaviour predates this PR, but this PR widens the gap between the
two policies.
The two want reconciling, or the difference wants documenting on both
functions. Changing `from_naive_datetime` affects every caller of
`arrow-array`, so it deserves its own review rather than a ride along
with a cast fix.
## Follow-up: a loud error becomes a quiet non-identity
`to_local_time(ts) AT TIME ZONE tz` is a documented DataFusion idiom.
Across an ambiguous hour it is no longer the identity, and it no longer
says so. Before this PR the second leg raised `Cannot cast timezone to
different timezone`:
```text
orig local roundtrip identity
2021-10-31T02:00:00+02:00 2021-10-31T02:00:00 2021-10-31T02:00:00+01:00 false
2021-10-31T02:30:00+02:00 2021-10-31T02:30:00 2021-10-31T02:30:00+01:00 false
2021-10-31T02:00:00+01:00 2021-10-31T02:00:00 2021-10-31T02:00:00+01:00 true
```
PostgreSQL behaves the same way. `t = ((t AT TIME ZONE
'Europe/Brussels') AT TIME ZONE 'Europe/Brussels')` is `f` for those
same two instants. So this is the intended trade, not an argument
against the change. But it is a real behaviour change, and downstream
docs want to say so.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Fable 5.1 <[email protected]>
Co-authored-by: Jeffrey Vo <[email protected]>1 parent fdeada1 commit 2166741
2 files changed
Lines changed: 265 additions & 8 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
55 | 55 | | |
56 | 56 | | |
57 | 57 | | |
| 58 | + | |
| 59 | + | |
| 60 | + | |
58 | 61 | | |
59 | 62 | | |
60 | 63 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
57 | 57 | | |
58 | 58 | | |
59 | 59 | | |
60 | | - | |
| 60 | + | |
| 61 | + | |
| 62 | + | |
61 | 63 | | |
62 | 64 | | |
63 | 65 | | |
| |||
1921 | 1923 | | |
1922 | 1924 | | |
1923 | 1925 | | |
| 1926 | + | |
| 1927 | + | |
| 1928 | + | |
| 1929 | + | |
| 1930 | + | |
1924 | 1931 | | |
1925 | 1932 | | |
1926 | 1933 | | |
| |||
2600 | 2607 | | |
2601 | 2608 | | |
2602 | 2609 | | |
| 2610 | + | |
| 2611 | + | |
| 2612 | + | |
| 2613 | + | |
| 2614 | + | |
| 2615 | + | |
| 2616 | + | |
| 2617 | + | |
| 2618 | + | |
| 2619 | + | |
| 2620 | + | |
| 2621 | + | |
| 2622 | + | |
| 2623 | + | |
| 2624 | + | |
| 2625 | + | |
| 2626 | + | |
| 2627 | + | |
| 2628 | + | |
| 2629 | + | |
| 2630 | + | |
| 2631 | + | |
| 2632 | + | |
| 2633 | + | |
| 2634 | + | |
| 2635 | + | |
| 2636 | + | |
| 2637 | + | |
| 2638 | + | |
| 2639 | + | |
| 2640 | + | |
| 2641 | + | |
| 2642 | + | |
| 2643 | + | |
| 2644 | + | |
| 2645 | + | |
| 2646 | + | |
| 2647 | + | |
| 2648 | + | |
| 2649 | + | |
| 2650 | + | |
| 2651 | + | |
| 2652 | + | |
| 2653 | + | |
| 2654 | + | |
| 2655 | + | |
| 2656 | + | |
| 2657 | + | |
| 2658 | + | |
| 2659 | + | |
| 2660 | + | |
| 2661 | + | |
| 2662 | + | |
| 2663 | + | |
| 2664 | + | |
| 2665 | + | |
| 2666 | + | |
| 2667 | + | |
| 2668 | + | |
2603 | 2669 | | |
2604 | 2670 | | |
2605 | 2671 | | |
2606 | 2672 | | |
2607 | 2673 | | |
2608 | 2674 | | |
2609 | 2675 | | |
2610 | | - | |
2611 | | - | |
| 2676 | + | |
| 2677 | + | |
2612 | 2678 | | |
2613 | 2679 | | |
2614 | 2680 | | |
| |||
6956 | 7022 | | |
6957 | 7023 | | |
6958 | 7024 | | |
| 7025 | + | |
| 7026 | + | |
| 7027 | + | |
| 7028 | + | |
| 7029 | + | |
| 7030 | + | |
| 7031 | + | |
| 7032 | + | |
| 7033 | + | |
| 7034 | + | |
| 7035 | + | |
| 7036 | + | |
| 7037 | + | |
| 7038 | + | |
| 7039 | + | |
| 7040 | + | |
| 7041 | + | |
| 7042 | + | |
| 7043 | + | |
| 7044 | + | |
| 7045 | + | |
| 7046 | + | |
| 7047 | + | |
| 7048 | + | |
| 7049 | + | |
| 7050 | + | |
| 7051 | + | |
| 7052 | + | |
| 7053 | + | |
| 7054 | + | |
| 7055 | + | |
| 7056 | + | |
| 7057 | + | |
| 7058 | + | |
| 7059 | + | |
| 7060 | + | |
| 7061 | + | |
| 7062 | + | |
| 7063 | + | |
| 7064 | + | |
| 7065 | + | |
| 7066 | + | |
| 7067 | + | |
| 7068 | + | |
| 7069 | + | |
| 7070 | + | |
| 7071 | + | |
| 7072 | + | |
| 7073 | + | |
| 7074 | + | |
| 7075 | + | |
| 7076 | + | |
| 7077 | + | |
| 7078 | + | |
| 7079 | + | |
| 7080 | + | |
| 7081 | + | |
| 7082 | + | |
| 7083 | + | |
| 7084 | + | |
| 7085 | + | |
| 7086 | + | |
| 7087 | + | |
| 7088 | + | |
| 7089 | + | |
| 7090 | + | |
| 7091 | + | |
| 7092 | + | |
| 7093 | + | |
| 7094 | + | |
| 7095 | + | |
| 7096 | + | |
| 7097 | + | |
| 7098 | + | |
| 7099 | + | |
| 7100 | + | |
| 7101 | + | |
| 7102 | + | |
| 7103 | + | |
| 7104 | + | |
| 7105 | + | |
| 7106 | + | |
| 7107 | + | |
| 7108 | + | |
| 7109 | + | |
| 7110 | + | |
| 7111 | + | |
| 7112 | + | |
| 7113 | + | |
| 7114 | + | |
| 7115 | + | |
| 7116 | + | |
| 7117 | + | |
| 7118 | + | |
| 7119 | + | |
| 7120 | + | |
| 7121 | + | |
| 7122 | + | |
| 7123 | + | |
| 7124 | + | |
| 7125 | + | |
| 7126 | + | |
| 7127 | + | |
| 7128 | + | |
| 7129 | + | |
| 7130 | + | |
| 7131 | + | |
| 7132 | + | |
| 7133 | + | |
| 7134 | + | |
| 7135 | + | |
| 7136 | + | |
| 7137 | + | |
| 7138 | + | |
| 7139 | + | |
| 7140 | + | |
| 7141 | + | |
| 7142 | + | |
| 7143 | + | |
| 7144 | + | |
| 7145 | + | |
| 7146 | + | |
| 7147 | + | |
| 7148 | + | |
| 7149 | + | |
| 7150 | + | |
| 7151 | + | |
| 7152 | + | |
| 7153 | + | |
| 7154 | + | |
| 7155 | + | |
| 7156 | + | |
| 7157 | + | |
| 7158 | + | |
| 7159 | + | |
| 7160 | + | |
| 7161 | + | |
| 7162 | + | |
| 7163 | + | |
| 7164 | + | |
| 7165 | + | |
| 7166 | + | |
| 7167 | + | |
| 7168 | + | |
| 7169 | + | |
| 7170 | + | |
| 7171 | + | |
| 7172 | + | |
| 7173 | + | |
| 7174 | + | |
| 7175 | + | |
| 7176 | + | |
| 7177 | + | |
| 7178 | + | |
| 7179 | + | |
| 7180 | + | |
| 7181 | + | |
| 7182 | + | |
| 7183 | + | |
| 7184 | + | |
| 7185 | + | |
| 7186 | + | |
| 7187 | + | |
| 7188 | + | |
| 7189 | + | |
| 7190 | + | |
| 7191 | + | |
| 7192 | + | |
| 7193 | + | |
| 7194 | + | |
| 7195 | + | |
| 7196 | + | |
| 7197 | + | |
| 7198 | + | |
| 7199 | + | |
| 7200 | + | |
| 7201 | + | |
| 7202 | + | |
| 7203 | + | |
| 7204 | + | |
| 7205 | + | |
| 7206 | + | |
| 7207 | + | |
| 7208 | + | |
| 7209 | + | |
6959 | 7210 | | |
6960 | 7211 | | |
6961 | 7212 | | |
| |||
12598 | 12849 | | |
12599 | 12850 | | |
12600 | 12851 | | |
12601 | | - | |
12602 | | - | |
12603 | | - | |
12604 | | - | |
12605 | | - | |
| 12852 | + | |
| 12853 | + | |
| 12854 | + | |
| 12855 | + | |
| 12856 | + | |
| 12857 | + | |
| 12858 | + | |
| 12859 | + | |
12606 | 12860 | | |
12607 | 12861 | | |
12608 | 12862 | | |
| |||
0 commit comments