`TIMESTAMPTZ` → Python conversion (`fetchone`/`.df()`/`.arrow()`) returns wrong DST offset for dates from 2038 onward (pytz Year 2038 bug)

[data-gym]

What happens?

When a TIMESTAMPTZ value is materialized into Python — via fetchone()/fetchall(), .df(), or .arrow() — for any date from 2038 onward during a DST period, the returned offset is the zone's standard offset instead of its DST offset. The underlying stored value (the UTC instant / epoch) is correct; only the Python-side conversion is wrong.

This makes .df()/.arrow() results silently wrong by exactly one hour for any DST-period timestamp dated 2038+ — the local wall-clock time attached to the returned value is shifted, even though SELECT ... ::VARCHAR and ... AT TIME ZONE 'UTC' both show the correct value for the same row.

I traced this to duckdb::PytzCacheItem in the compiled Python extension (symbol visible via strings on _duckdb.cpython-*.so) — DuckDB's Python client builds the returned tzinfo via pytz, and pytz has a known, still-unresolved Year 2038 defect in its DST-transition lookup for zone-name-based localization. I confirmed the identical wrong offset by calling pytz directly, with no DuckDB involved at all (see repro below) — so this isn't a DuckDB-specific timezone calculation bug, it's DuckDB inheriting a known pytz defect through its Python conversion layer.

This is the same class of defect as the open Apache Arrow issue apache/arrow#36110 ("Wrong result when converting time zones after 2038"), which affects PyArrow's assume_timezone() for the same reason (offset resolution breaking at the 32-bit Unix-time boundary, 2038-01-19T03:14:07Z). We hit this in production after having already migrated away from DuckDB's make_timestamptz() to PyArrow specifically to avoid this bug class, only to discover PyArrow has an identical defect — and now find DuckDB's own Python client reintroduces it through pytz.

To Reproduce

import duckdb

con = duckdb.connect()
con.execute("INSTALL icu")
con.execute("LOAD icu")
con.execute("SET TimeZone='America/Chicago'")

for year in (2037, 2038, 2039):
    sql_repr = con.execute(
        f"SELECT make_timestamptz({year}, 8, 1, 3, 0, 0)::VARCHAR"
    ).fetchone()[0]
    py_obj = con.execute(
        f"SELECT make_timestamptz({year}, 8, 1, 3, 0, 0)"
    ).fetchone()[0]
    print(year, "| SQL:", sql_repr, "| Python:", repr(py_obj))

Output (DuckDB 1.5.3, pytz 2026.2):

2037 | SQL: 2037-08-01 03:00:00-05 | Python: datetime.datetime(2037, 8, 1, 3, 0, tzinfo=<DstTzInfo 'America/Chicago' CDT-1 day, 19:00:00 DST>)
2038 | SQL: 2038-08-01 03:00:00-05 | Python: datetime.datetime(2038, 8, 1, 2, 0, tzinfo=<DstTzInfo 'America/Chicago' CST-1 day, 18:00:00 STD>)
2039 | SQL: 2039-08-01 03:00:00-05 | Python: datetime.datetime(2039, 8, 1, 2, 0, tzinfo=<DstTzInfo 'America/Chicago' CST-1 day, 18:00:00 STD>)

Note the SQL-level VARCHAR cast correctly shows -05 (CDT) for all three years, but the Python datetime object returned for the same row silently switches to CST/-06:00 starting in 2038, and the local hour is wrong (2 instead of 3) as a result.

Same bug reproduces via .df() and .arrow():

df = con.execute(
    "SELECT year, make_timestamptz(year, 8, 1, 3, 0, 0) AS ts "
    "FROM (VALUES (2037),(2038),(2039)) t(year)"
).df()
print(df)
#    year                        ts
# 0  2037 2037-08-01 03:00:00-05:00
# 1  2038 2038-08-01 02:00:00-06:00   <- wrong, should be 03:00:00-05:00
# 2  2039 2039-08-01 02:00:00-06:00   <- wrong, should be 03:00:00-05:00

con.execute(...).arrow() on the same query produces identical (wrong) values.

For comparison, calling pytz directly — no DuckDB involved — reproduces the exact same wrong offset, confirming the root cause:

import pytz
from datetime import datetime, timezone

tz = pytz.timezone("America/Chicago")
for year in (2037, 2038, 2039):
    utc_dt = datetime(year, 8, 1, 8, 0, 0, tzinfo=timezone.utc)
    print(year, utc_dt.astimezone(tz))
# 2037 2037-08-01 03:00:00-05:00
# 2038 2038-08-01 02:00:00-06:00   <- wrong
# 2039 2039-08-01 02:00:00-06:00   <- wrong

And with stdlib zoneinfo instead of pytz, the correct offset is returned for every year:

import zoneinfo
tz = zoneinfo.ZoneInfo("America/Chicago")
for year in (2037, 2038, 2039):
    utc_dt = datetime(year, 8, 1, 8, 0, 0, tzinfo=timezone.utc)
    print(year, utc_dt.astimezone(tz))
# 2037 2037-08-01 03:00:00-05:00
# 2038 2038-08-01 03:00:00-05:00   <- correct
# 2039 2039-08-01 03:00:00-05:00   <- correct

OS:

Linux

DuckDB Version:

1.5.3 (Python client duckdb==1.5.3)

DuckDB Client:

Python

Hardware:

No response

Full Name:

Marc Macleod

Affiliation:

Ascend Analytics

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes

[f-hafner]

Cross-posting this feature request: #406

As suggested by the OP there, pytz should be replaced with the zoneinfo.