Cannot load timestamp from oracle when before '1677-09-22 00:00:00'

[lscheilling]

trafficstars

What language are you using?

Python

What version are you using?

Python 3.9 connectorx==0.3.4a1

What database are you using?

OracleDB

What dataframe are you using?

Polars

Can you describe your bug?

Seems to not be able to load timestamps before 1677-09-22 00:00:00

What are the steps to reproduce the behavior?

import connectorx as cx

connection = "oracle://..."

cx.read_sql(connection, "SELECT timestamp '1677-09-22 00:00:00' FROM table", return_type="polars2") # works
cx.read_sql(connection, "SELECT timestamp '1677-09-21 00:00:00' FROM table", return_type="polars2") # fails

Error: pyo3_runtime.PanicException: out of range DateTime

Database setup if the error only happens on specific data or data type

Included in example above (no specific table needed for this query)

Example query / code

import connectorx as cx

connection = "oracle://..."

cx.read_sql(connection, "SELECT timestamp '1677-09-22 00:00:00' FROM table", return_type="polars2") # works
cx.read_sql(connection, "SELECT timestamp '1677-09-21 00:00:00' FROM table", return_type="polars2") # fails
cx.read_sql(connection, "SELECT timestamp '1677-09-22 00:00:00' FROM table", return_type="polars") # works
cx.read_sql(connection, "SELECT timestamp '1677-09-21 00:00:00' FROM table", return_type="polars") # fails

What is the error?

pyo3_runtime.PanicException: out of range DateTime

[rudyryk]

Also related https://github.com/sfu-db/connector-x/issues/634

[jcoding2022]

I also had the same issue with reading date date from SQL Server, even with Version 0.4.0.

[pangjunrong]

Hey @lscheilling @rudyryk @jcoding2022, this is related to the limitations of pandas representing timestamps as a 64-bit integer, which means anything before '1677-09-21 00:12:43.145225' is out of bounds. When connector-x fetches the result and parses it into the appropriate pandas-equivalent data types (i.e. 64-bit integer for DateTime), PanicException is thrown because the dates provided must fall within the allowed range between '1677-09-21 00:12:43.145225' and '2262-04-11 23:47:16.854775807'.

You can read more about it here: https://pandas.pydata.org/pandas-docs/version/0.25.0/user_guide/timeseries.html#timestamp-limitations.

I'll be closing the issue.