Define and enforce the contract for Athena temporal types across cursors #1212

Summary

PyAthena has no defined contract for how each cursor represents Athena temporal values. Temporal fixes have been made one path at a time, and each exposed another gap (#1191, #1201). An audit of every cursor and read mode against live Athena found silent wrong values, data-dependent dtypes, and inconsistent errors.

This issue defines the contract for the temporal types (timestamp, timestamp with time zone, date, time, time with time zone). The sub-issues add conformance tests and fix the gaps against it. Other types will follow the same approach later.

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

• description reports precision 3 for every timestamp, including TIMESTAMP(0), (6), (9), and (12), so the precision of a column cannot be chosen from metadata.
• The CSV result file and GetQueryResults write the same text:
  • timestamp: 0 to 12 fractional digits. Years after 9999 are written with a sign and five or more digits (+11020-01-02 03:04:05.123), and years before 0001 with a sign (-0001-01-02 03:04:05.123).
  • timestamp with time zone: 2020-01-02 03:04:05.123 UTC, ... Asia/Tokyo, or ... +05:30.
  • time with time zone: 03:04:05.123+05:30.
  • NULL: an empty unquoted CSV field, or no VarCharValue.
• UNLOAD to Parquet supports only TIMESTAMP(3) (other precisions fail with NOT_SUPPORTED) and no time or with time zone types. It writes timestamps as INT96 (created_by: parquet-mr-trino). Decoding INT96 at microseconds (coerce_int96_timestamp_unit="us" in pyarrow) reads years 1600, 9999, +11020, and -0001 correctly.

Audit of the current behavior

Values outside years 1 to 9999 are out of scope (see contract item 3). The audit items about them are kept as measured, but no sub-issue tracks them.

Measured on master b2d7aa2 with Python 3.15, pandas 3.0.6, pyarrow 26.0.0, and Polars 2.0.0. Modes: Cursor, S3FSCursor, PandasCursor, ArrowCursor, and PolarsCursor, each read whole, chunked, through fetch methods, with unload=True, and with managed query result storage. Async cursors were not measured.

• Silent wrong values
  • With UNLOAD, timestamps outside 1677-09-21 to 2262-04-11 are wrong in every DataFrame cursor: pandas and Arrow wrap them (1600-01-02 03:04:05.123 → 2184-07-22 02:38:38.832551616, 9999-12-31 23:59:59.999 → 1816-03-30 05:56:08.065277376), and Polars saturates them to 2262-04-11 23:47:16.854775807.
  • PandasCursor reads year -0001 as year 1, for timestamp and date.
  • ArrowCursor reads NULL time and timestamp with time zone values as ''.
  • PolarsCursor truncates fractions at a fixed length that assumes a four-digit year (PolarsCursor truncates timestamp fractions wrongly for years outside 0000-9999 #1201).
• dtypes that depend on the data or the read mode
  • PandasCursor timestamp is datetime64[us] for up to 6 digits, [ns] for 9 or 12 digits, [s] when all NULL, and str when a year is +11020.
  • timestamp is us from CSV but ns from UNLOAD.
  • ArrowCursor date is timestamp[ms] from CSV but date32 from UNLOAD.
  • PandasCursor date is datetime64 from CSV but object (date) from UNLOAD.
• Errors
  • Cursor and S3FSCursor raise a bare ValueError for years 0000, negative, or five-digit.
  • ArrowCursor raises OperationalError for signed years.
  • PolarsCursor raises for 12 digits and signed years in chunked reads (Chunked PolarsCursor CSV reads fail on timestamps with more fractional digits than the time unit #1191).
  • Fetch methods raise OverflowError (ArrowCursor) or a pyo3 PanicException (PolarsCursor). The PanicException is a BaseException, which except Exception does not catch.

Contract

1. A column's dtype depends only on its Athena type and the cursor class. It does not depend on the values, the read mode (whole, chunked, fetch, UNLOAD, managed results), or sync versus async.
2. Microseconds are the canonical precision. Fractional digits beyond six are truncated, not rounded, including before 1970.
3. Typed values cover years 1 to 9999. Years outside that range are out of scope: PyAthena does not detect them, and whatever pandas, pyarrow, Polars, or Python returns or raises for them passes through.
4. SQL NULL is None in fetch methods and the container's null in DataFrames, never an empty string.
5. A conversion failure of a value in that range raises the DB API DataError, naming the column.
6. UNLOAD results have the same dtypes as CSV results.
7. Read paths stay native and vectorized. Changes are measured for elapsed time and peak RSS.
8. execute() options and custom converters keep their documented precedence. When a user option such as schema or ignore_errors changes how a value is read, whole and chunked reads behave the same.

Arrow and Polars have no per-row time zone type, so they keep timestamp with time zone as Athena's text. The fetch methods convert it to an aware datetime. Text columns keep every fractional digit.

Sub-issues

• Add temporal type tests for each synchronous cursor #1213: Conformance tests against live Athena output, with cases chosen by the code path that converts each value.
• UNLOAD timestamps outside 1677-2262 are read as wrong values #1214: Read UNLOAD timestamps at microseconds in PandasCursor, ArrowCursor, and PolarsCursor (values and dtype).
• Chunked PolarsCursor CSV reads fail on timestamps with more fractional digits than the time unit #1191: Chunked PolarsCursor CSV reads fail on timestamps with 12 fractional digits (Draft PR Read the timestamp columns of chunked Polars CSV results as text #1194).
• Read CSV dates and NULL temporal values in ArrowCursor as the contract defines #1216: ArrowCursor reads CSV date as date32 and NULL temporal values as null (including ArrowCursor reads NULL time and time zone values as empty strings #1218).
• Name the column in DataError for time zone values in every cursor #1217: Raise DataError naming the column for time zone errors, including PandasCursor.
• Make PandasCursor's temporal dtypes and fetched types depend only on the Athena type #1219: Make PandasCursor's temporal dtypes and fetched types depend only on the Athena type.
• Document the temporal type contract and gate the release on it #1221: Documentation and release notes.

Closed: #1215 (years outside 1 to 9999, out of scope), #1218 (moved into #1216), and #1220 (done in #1224).

Release plan

Every sub-issue ships in 4.2.0. There is no patch release.

• The release notes list the fixes that stop silent wrong values (UNLOAD, NULL as ''), each with its behavior change.
• Migration notes cover the breaking changes: dtype normalization, fetched types, and DataError for time zone errors.

The conformance tests come first. Each sub-issue removes its strict xfails when it lands.