Read and write timestamps at Athena's precision for each path #1289

PyAthena should read and write timestamps at the precision that Athena has for each path. Athena's precision differs between DDL (Hive) and DML (Trino), so the precision depends on the path, and a reader or writer that uses a precision Athena does not have can return wrong values.

Athena's precision

Measured on 2026-10-18 with the test work group:

UNLOAD results therefore hold milliseconds. Reading them at microseconds, as documented for every cursor, loses nothing; reading them at nanoseconds gives wrong values outside 1677-09-21 to 2262-04-11.

Paths that do not follow it

1. PolarsCursor with Polars 1.x reads UNLOAD timestamps at nanoseconds

Polars 1.x reads INT96 only at nanoseconds. PolarsCursor casts the result to microseconds and logs a warning (#1280, #1282), but values outside 1677-09-21 to 2262-04-11 are wrong. Only chunked reads with pyarrow installed and no read options read them correctly. Polars 2.0 and later read them at microseconds.

2. PandasCursor: coerce_int96_timestamp_unit=None reads UNLOAD timestamps at nanoseconds

AthenaPandasResultSet._read_parquet() sets coerce_int96_timestamp_unit="us", and an option given to execute() replaces it. None is pyarrow's default, which reads at nanoseconds. This is the same kind of defect as #1283 (PolarsCursor schema=None).

cursor = connect(cursor_class=PandasCursor).cursor(unload=True)
q = "SELECT TIMESTAMP '1600-01-01 00:00:00.000' AS ts"
cursor.execute(q).as_pandas()["ts"]
# datetime64[us]  [Timestamp('1600-01-01 00:00:00')]
cursor.execute(q, coerce_int96_timestamp_unit=None).as_pandas()["ts"]
# datetime64[ns]  [Timestamp('2184-07-20 23:34:33.709551616')]

_finish_parquet_frame() and _get_fetch_types() also treat the presence of the option, whatever its value, as given.

3. pyathena.pandas.util.to_sql() writes INT96 timestamps that pyarrow 22 wraps

to_parquet() writes with flavor="spark", which writes timestamps as INT96. With pyarrow 22.0.0, the supported minimum, a datetime64[us] value 1600-01-02 03:04:05.123456 is written as 2184-07-22 02:38:38.833007; pyarrow 26.0.0 writes it correctly. This was measured locally by reading the file back with coerce_int96_timestamp_unit="us" (pandas 3.0.6). Not yet checked: the first pyarrow version that writes it correctly, and the values that Athena reads from such a file into a Hive table, whose precision is milliseconds.