Repository navigation
Make PandasCursor's temporal dtypes and fetched types depend only on the Athena type #1219
Description
Part of #1212.
Summary
Make PandasCursor's temporal dtypes and fetched types depend only on the Athena type, as in the contract table of #1212. This is a breaking change; it ships in 4.2.0 with migration notes.
Facts
PandasCursor fixes the dtypes of numeric and string columns with dtype, but not those of date, time, and timestamp. It passes them as parse_dates, so pandas infers the unit from the values, again for each chunk, and keeps a column as text when a value does not parse.
Changes
timestampis alwaysdatetime64[us]. Today it is[s]without fractions (thepyarrowengine) or when all NULL,[ns]for 7 or more fractional digits,strwhen such a column also holds values outside the nanosecond range, and different per chunk.- pandas 3.0.6 cannot take
datetime64[us]as aread_csvdtype. Reading the text, truncating fractions to six digits, and then callingto_datetime(format="ISO8601").dt.as_unit("us")gavedatetime64[us]for every case above.
- pandas 3.0.6 cannot take
timestamp with time zoneisdatetime64[us, UTC]. Today the dtype carries the zone of the data, such asdatetime64[us, Asia/Tokyo], and a column with two zones isobject.dateisdatetime64[us]from both CSV and UNLOAD. Today UNLOAD givesobject(date).- The fetch methods return
datefordate, andNonefor NULLtimestampanddatevalues. Today they returnTimestamp, andNaTornan.
Each change keeps reads vectorized and is measured for elapsed time and peak RSS. execute() options and custom converters keep their documented precedence. UNLOAD timestamp is tracked in #1214.
Activity
laughingman7743 commentedon Oct 16, 2026
The conformance tests in #1226 found more cases for this issue with mixed columns (live Athena output, pandas 3.0.6). They are strict xfails that reference this issue:
timestampcolumn whose text has 7 or more fractional digits isstrwhen the column also holds values outside thedatetime64[ns]range, such as years 1 and 9999. Chunked reads givestr,object, ordatetime64[ns]chunks, and the[ns]chunks keep nanoseconds instead of truncating to microseconds.engine="pyarrow", atimestamp(0)column isdatetime64[s].NaT, ornanfor astrcolumn, instead ofNonefor NULLtimestampanddatevalues. Atimestamp with time zonecolumn whose values share one zone returnsNaTtoo.[-]Make temporal dtypes depend only on the Athena type[/-][+]Make PandasCursor's temporal dtypes and fetched types depend only on the Athena type[/+]Expect the UNLOAD temporal types to pass
Expect the UNLOAD temporal types to pass
Expect the UNLOAD temporal types to pass
laughingman7743 commentedon Oct 18, 2026
A fact for the
timestampitem, measured with pandas 3.0.6 and pyarrow 26.0.0 at 3a41a84. Withengine="pyarrow", the unit of atimestampcolumn depends on whether the values have a fraction, not on its length: a result file of 100 bytes or more, which PyAthena reads with its own PyArrow reader, givesdatetime64[ns]for any fraction of 1 to 9 digits. Thecandpythonengines givedatetime64[us]for up to six digits anddatetime64[ns]for nine. Athena writesTIMESTAMPvalues with three fractional digits, so withengine="pyarrow", atimestampcolumn of a real result isdatetime64[ns].engine="pyarrow"engine="c"and"python"2024-01-01 00:00:00datetime64[s]datetime64[us]2024-01-01 00:00:00.000datetime64[ns]datetime64[us]2024-01-01 00:00:00.123datetime64[ns]datetime64[us]2024-01-01 00:00:00.123456datetime64[ns]datetime64[us]2024-01-01 00:00:00.123456789datetime64[ns]datetime64[ns]Against Athena,
SELECT TIMESTAMP '2024-01-01 00:00:00.123' AS t, CAST(NULL AS TIMESTAMP) AS t_null, rpad('x', 120, 'x') AS pread withPandasCursorgivestasdatetime64[ns]withengine="pyarrow"anddatetime64[us]withengine="c";t_nullisdatetime64[s]with both. The table rows were measured offline throughAthenaPandasResultSetover local result files (the_local_result_set()helper of #1256).Convert early local times, group zones once, and share the label disp…
Test early local times, conversion errors, engine-independent time zo…
Describe the temporal conversion precedence, errors, and iterator rows
Ignore pandas' converter-and-dtype warning in the engine test
Keep NULL in every engine, take positional and mapping options, and l…
Resolve dtype and converters keys of temporal columns as pandas does
Test that the PyArrow engine takes no dtype positions of temporal col…
3 remaining items
Expect datetime64 for the UNLOAD date column of the complex pyarrow test
Give PandasCursor's temporal columns one dtype per Athena type
Describe PandasCursor's temporal dtypes and fetched types
Convert early local times, group zones once, and share the label disp…
Test early local times, conversion errors, engine-independent time zo…
Describe the temporal conversion precedence, errors, and iterator rows
Ignore pandas' converter-and-dtype warning in the engine test
Keep NULL in every engine, take positional and mapping options, and l…
Resolve dtype and converters keys of temporal columns as pandas does
Test that the PyArrow engine takes no dtype positions of temporal col…
Leave temporal columns to pandas with parse_dates, dtype, or converters
Treat a None parse_dates, dtype, or converters as not given
Check that converters of None keep the PyArrow engine
Expect datetime64 for the UNLOAD date column of the complex pyarrow test