Define time zone conversion for values whose zone cannot be represented #1220

Activity

1. laughingman7743

   added

   enhancement

   on Oct 16, 2026
2. laughingman7743

   added a parent issue on Oct 16, 2026

   • Define and enforce the contract for Athena temporal types across cursors #1212
3. laughingman7743 commented on Oct 16, 2026

   

   laughingman7743

   on Oct 16, 2026

   Last edited by laughingman7743

   MemberAuthor

   More actions

   Measurements (live Athena engine v3, 2026-10-17)

   • Named zones come only from SQL expressions. An Iceberg timestamp(6) with time zone column stores the instant and reads back as ... UTC, whatever zone was inserted. Hive tables have no time zone type. The session time zone is UTC. A named zone appears in a result only when the query produces one, for example with at_timezone(), AT TIME ZONE, with_timezone(), or from_unixtime(x, zone).

   • The DST fall-back hour is written ambiguously. 2020-11-01 05:30 UTC and 06:30 UTC both print as 2020-11-01 01:30:00 America/New_York. The same holds for Europe/London and Australia/Lord_Howe (30-minute DST). Athena parses that text, as a literal or through with_timezone(), as the earlier instant, which is Python's fold=0.

   • Athena never writes a nonexistent local time. It formats from the instant. A literal in the DST gap is rejected with INVALID_LITERAL, and with_timezone() shifts it forward. A nonexistent time can appear only when Athena's and Python's time zone rules differ.

   • Athena's time zone database is older than tzdata 2025a. It rejects America/Coyhaique (2025b) and uses the earlier Paraguay rules. For 549 zone keys × 10 instants from 1600 to 9999, the instant Python computes from Athena's text differed from to_unixtime() in 11 cases:

     • America/Asuncion from 2026: 1 hour, with macOS's 2026c database.
     • Africa/Casablanca and Africa/El_Aaiun from 2038: 1 hour.
     • Asia/Manila in 1600: 8 seconds.

     The text carries no offset, so these differences cannot be detected.

   • Unknown zone names. Every zone key in Trino's zone index except America/Coyhaique, which Athena rejects, is in tzdata 2024b. Python reports a zone name as unknown only when its time zone database is older than Athena's. zoneinfo searches the system TZPATH before the tzdata package.

   • time with time zone always carries an offset (03:04:05.123-04:00). TIME '... America/New_York' is rejected, and a cast from a named-zone timestamp yields an offset. Offsets range from -14:00 to +14:00, in minutes.

   • Zone keys are written as given. US/Eastern stays US/Eastern, UTC+3 becomes +03:00, Etc/GMT+3 becomes -03:00, and Z becomes UTC.

   • pandas 3.0.6. An object column with one zone infers datetime64[us, <zone>], and one with two zones stays object. tz_localize(ambiguous="raise", nonexistent="raise") detects both cases without converting row by row.

   Decision

   Case	Fetch methods and PandasCursor
   Offset	Exact: a fixed-offset timezone
   Zone name known to Python, including UTC	ZoneInfo
   Zone name unknown to Python's time zone database, including an environment without time zone data	DataError naming the column and the zone, suggesting an update of tzdata
   Ambiguous local time (DST fall-back)	DataError naming the column, suggesting AT TIME ZONE 'UTC' or to_unixtime()
   Nonexistent local time (DST gap)	DataError naming the column
   Rules that differ between Athena and Python	Not detectable. The value is converted by Python's rules, and the limitation is documented with the same SQL workaround.
   Two or more zones in one PandasCursor column	datetime64[us, UTC], as in #1212
   time with time zone	Aware time with a fixed-offset timezone

   • No option returns fold=0 for ambiguous values. Named zones come only from the user's SQL, and converting there gives the exact instant. fold=0 would return the wrong instant for half of the values in that hour.
   • Arrow and Polars keep these columns as text, so these cases apply only where values are converted: fetch methods and PandasCursor.
   • Add the dependency floor tzdata>=2024.2 (IANA 2024b) so that every zone name Athena accepts today is known. The floor does not make the rules match.

   All of these ship in 4.2.0. Implementation: DataError cases in the shared converter and the tzdata floor in this issue's PR; the column name in the error in #1217; PandasCursor UTC normalization with vectorized ambiguity checks in #1219; documentation of the limitation and the SQL workaround in #1221.
4. laughingman7743

   added a commit that references this issue on Oct 16, 2026

   Cover the time zone cases decided in #1220

   ...

   895d1c5
5. laughingman7743

   closed this as completedin #1224on Oct 17, 2026
6. laughingman7743

   added 2 commits that reference this issue on Oct 17, 2026

   Cover the time zone cases decided in #1220

   ...

   067c62d

   Follow the time zone errors of #1220 and simulate an older tzdata

   ...

   4936167