[Bug] Timezone offset diverges from Spark: DST not extrapolated past 2037, and legacy uses LMT before standardization

[zhangxffff]

Component Selection

• Core Engine (Expression eval, Memory, Vector)
• Connectors / File Formats (Hive, Parquet, etc.)
• API / Bindings (Python, etc.)
• Build
• Other

Describe the Bug

Timezone offset resolution diverges from Spark in two independent cases, both in the timezone layer (not in year/format handling). Differential testing of 325,730 from_unixtime cases against Java ground truth (java.time / SimpleDateFormat, itself verified against real Spark 3.2.4 on 162,750 cases) leaves 6,756 mismatches, all of them timezone-offset differences:

1. DST rule not extrapolated past 2037 (≈6,539 cases, corrected and legacy).
For DST zones such as America/Los_Angeles, summer instants from 2038 onward use the standard-time offset (PST, −8) instead of the daylight offset (PDT, −7), i.e. Bolt is 1 hour behind Spark/java.time. The 32-bit tzfile transition table ends in 2037; Spark (java.time) keeps applying the POSIX TZ footer rule (PST8PDT,M3.2.0,M11.1.0) indefinitely, while Bolt appears to freeze on the last transition's offset. With a date-only pattern the 1-hour shift also moves the day/month back by one.

2. Legacy policy uses the LMT offset before 1883/1901 instead of standard time (≈217 cases, legacy only).
For pre-1883 America/Los_Angeles and pre-1901 Asia/Shanghai, under LEGACY policy Bolt formats with the IANA Local Mean Time offset (LA −7:52:58, Shanghai +8:05:43), while Spark's legacy path (SimpleDateFormat / java.util.TimeZone) uses the whole standard-time offset (−8:00 / +8:00). Under CORRECTED policy both use LMT and agree — the divergence is legacy-only.

Reproduction Steps

Verified against real Spark 3.2.4 (values below are from_unixtime(sec, 'yyyy-MM-dd HH:mm:ss')):

set spark.sql.session.timeZone=America/Los_Angeles;

-- (1) post-2037 DST, corrected policy
set spark.sql.legacy.timeParserPolicy=CORRECTED;
select from_unixtime(1909137600,  'yyyy-MM-dd HH:mm:ss');  -- 2030 summer
-- Spark 2030-07-01 05:00:00   Bolt 2030-07-01 05:00:00   (agree; before 2038)
select from_unixtime(3360225600,  'yyyy-MM-dd HH:mm:ss');  -- 2076 summer
-- Spark 2076-06-24 05:00:00   Bolt 2076-06-24 04:00:00   (Bolt 1h behind)
select from_unixtime(4118126400,  'yyyy-MM-dd HH:mm:ss');  -- 2100 summer
-- Spark 2100-07-01 05:00:00   Bolt 2100-07-01 04:00:00   (Bolt 1h behind)

-- (2) pre-1883 LMT, legacy vs corrected
set spark.sql.legacy.timeParserPolicy=CORRECTED;
select from_unixtime(-12212553600, 'yyyy-MM-dd HH:mm:ss');
-- Spark 1582-12-31 16:07:02   Bolt 1582-12-31 16:07:02   (agree; both LMT)
set spark.sql.legacy.timeParserPolicy=LEGACY;
select from_unixtime(-12212553600, 'yyyy-MM-dd HH:mm:ss');
-- Spark 1582-12-31 16:00:00   Bolt 1582-12-31 16:07:02   (Bolt uses LMT, Spark standard)

Shanghai counterpart (also real Spark 3.2.4, legacy):

set spark.sql.session.timeZone=Asia/Shanghai;
set spark.sql.legacy.timeParserPolicy=LEGACY;
select from_unixtime(-12212553600, 'yyyy-MM-dd HH:mm:ss');
-- Spark 1583-01-01 08:00:00   Bolt 1583-01-01 08:05:43

Bolt Version / Commit ID

main branch @ 625ee2e

System Configuration

• OS: Debian (Linux 6.12)
• Compiler: GCC (Release)
• Build Type: Release
• CPU Arch: x86_64 AVX2
• Framework: Spark (SPARK_COMPATIBLE, USE_OS_TZDB); reference: Spark 3.2.4 (JDK 11) and java.time / SimpleDateFormat

Expected Behavior

Timezone offsets match Spark: (1) DST rules extrapolate past 2037 via the POSIX TZ footer, so summer instants keep the daylight offset; (2) under legacy policy, pre-standardization instants use the whole standard-time offset (matching java.util.TimeZone / SimpleDateFormat), not the IANA LMT offset.

Logs / Stack Trace

Differential (325,730 cases, from_unixtime, vs Java ground truth verified against Spark 3.2.4):
  match 318,974 / 325,730 (97.93%)  — 0 year/sign diffs
  6,756 timezone-offset diffs:
    post-2037 DST not extrapolated : ~6,539  (America/Los_Angeles, corrected + legacy)
    pre-1883/1901 LMT under legacy :   217   (LA 145 + Shanghai 72, legacy only)

Additional context

Root cause is in the timezone layer, unrelated to the timestamp-range extension (12703b6) or the year-formatting fixes (#948, #963). Bolt uses the date/tz library with USE_OS_TZDB; symptom (1) is consistent with that library not applying the tzfile's POSIX TZ footer rule beyond the 32-bit transition table, and symptom (2) with the toGMT/toTimezone path using the tzdb LMT offset even under legacy policy, where Spark mirrors java.util.TimeZone (no sub-minute historical offsets). Both are pre-existing and out of scope for the year-formatting work; filing separately as suggested.