Strange error "ValueError: year -1 is out of range" when reading timestamp

[jammann]

I'm trying to read from an Oracle table (19c server) with a TIMESTAMP(6) column, which contains values that are displayed as "01-JAN-01 01.00.00.000000000 AM". Those values have been inserted by a Java application to indicate "beginning of time".

I'm reading this table from a Python script (3.11.8, oracledb version 2.1.1, thick driver (19, 13, 0, 0, 0)). Trying to read the rows which contains such values results in

File /opt/fqbpython/envs/pandas-kernel/lib/python3.11/site-packages/sqlalchemy/engine/cursor.py:1135, in CursorFetchStrategy.fetchall(self, result, dbapi_cursor)
    1129 def fetchall(
    1130     self,
    1131     result: CursorResult[Any],
    1132     dbapi_cursor: DBAPICursor,
    1133 ) -> Any:
    1134     try:
--> 1135         rows = dbapi_cursor.fetchall()
    1136         result._soft_close()
    1137         return rows

File /opt/fqbpython/envs/pandas-kernel/lib/python3.11/site-packages/oracledb/cursor.py:770, in Cursor.fetchall(self)
    768 fetch_next_row = self._impl.fetch_next_row
    769 while True:
--> 770     row = fetch_next_row(self)
    771     if row is None:
    772         break
File src/oracledb/impl/base/cursor.pyx:552, in oracledb.base_impl.BaseCursorImpl.fetch_next_row()
File src/oracledb/impl/base/cursor.pyx:260, in oracledb.base_impl.BaseCursorImpl._create_row()
File src/oracledb/impl/thick/var.pyx:132, in oracledb.thick_impl.ThickVarImpl._get_scalar_value()
File src/oracledb/impl/thick/var.pyx:278, in oracledb.thick_impl.ThickVarImpl._transform_element_to_python()
File src/oracledb/impl/thick/utils.pyx:339, in oracledb.thick_impl._convert_to_python()
File datetime.pxd:298, in cpython.datetime.datetime_new()

ValueError: year -1 is out of range

I tried to create a minimal testcase for this and came up with the following (executed in SQL*CL)

create table python_tsd_test ( ts timestamp(6) );
INSERT into python_tsd_test (ts) values (TO_TIMESTAMP('0001/01/01 01:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

Suprisingly (to me) reading this table/column worked nicely from Python. Then I tried to add

INSERT into python_tsd_test (ts) select <origcol> from <origtable> where id = '123';

Which inserted a second row which looks exactly like the first row

SQL> select * from python_tsd_test;
TS                                
__________________________________
01-JAN-01 01.00.00.000000000 AM   
01-JAN-01 01.00.00.000000000 AM 

And now the "ValueError: year -1 is out of range" occurs again when reading both rows from Python! I can't understand what is the difference between those 2 rows, and why one of them makes the oraceldb library fail.

Any idea?

CU, Joe

[anthony-tuininga]

As a suggestion, try getting the value of dump() for both values. If the year is indeed negative that won't show in the default date format for display! You can also try using the format SYYYY-MM-DD.

[jammann]

Thanks for those hints, Anthony. I'm learning more about Oracle internal representations that I ever wanted to know :-)

Indeed the year is negative, as dump() shows (https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals#timestamp)

TS                         | DUMP(TS)
0001/01/01 01:00:00.000000 | Typ=180 Len=7: 100,101,1,1,2,1,1
0001/01/01 01:00:00.000000 | Typ=180 Len=7: 100,99,1,1,2,1,1

So I guess there is very little I can do about this. The Java (thin) driver seems to happily accept and generate this kind of timestamps, whereas the Python driver is more picky here (rightfully so, seems to me).

It only affects a few columns, so I will try to rewrite the SQL queries in a way that these invalid dates are converted into something that the Python driver can work with.

Thanks again for your help!

[anthony-tuininga]

You're welcome! Glad to be of help. :-)