B.C./Negative Year Dates

[bschwab003]

I am getting an unexpected response when extracting B.C. dates and think it might be some sort of arithmetic overflow error (potentially, speculating a bit)...

Below is the queries run in SQL Developer and what is being returned (in Python). Obviously, the main concern is the year coming back as 60824

SQL Developer Queries

Python object returned in fetch()/fetchmany()
datetime.datetime(60824, 1, 1, 0, 0)).

[anthony-tuininga]

It turns out that Python's datetime module only supports AD dates. See here and note the description of MINYEAR which is 1. If you subtract 4712 from 65536 you get 60824. I'll add some code to cx_Oracle to raise an exception when an unsupported date is returned instead of simply returning a bogus date!

[bschwab003]

Fair enough. Thank you. I suppose I could create a custom outputtypehandler to potentially handle B.C. dates in a custom manner?

[anthony-tuininga]

You're welcome. And yes, you could use an output type handler to return the date as a string and then do something custom with it.

[anthony-tuininga]

I just checked with Python 3.6 and it raises the exception: ValueError: year -4712 is out of range. I've just added code that does the same for Python 2.7.

[iamshubh95]

Hi I am facing the same issue, i created output type handler to convert the negative timestamp to string but it is still not returning - year, instead it it returning the year without minus sign, could you help how to get whether the year is negative or not using output type handler

i am returning this from type handler
return cursor.var(cx_Oracle.STRING,100,cursor.arraysize) for every timestamp column in my result.
the value is db is "-0001-01-01 00:00:00"
and this is returning me
0001-01-01 00:00:00

[cjbj]

@iamshubh95 show the SQL to create the table column, data in the table and a runnable python testcase. Thanks.

[iamshubh95]

@cjbj
table sql - create table table1(col1 timestamp(6));
insert into table1 values(to_timestamp('-0001-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS'))
and was fetching this data using cx_oracle
query= 'select * from table1'
which was returning
datetime.datetime(65535,1,1,0,0,0)

then i created output type handler for changing this timestamp to string using the code i already have written
then it was returning
'01/01/0001 12:00:00 AM'

it isnt giving back the year with minus

[cjbj]

The TIMESTAMP doc doesn't show a leading "-" is allowed for timestamps.

Also, SQL*Plus returns the date you see with a type handler.

SQL> select * from table1;

COL1
---------------------------------------------------------------------------
01-JAN-01 12.00.00.000000 AM

so this doesn't seem a cx_Oracle issue.

Intervals seem OK. In Python 3, a query:

sql = "select to_timestamp('0001-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') - to_timestamp('0001-01-02 00:00:00','SYYYY-MM-DD HH24:MI:SS') from dual"

prints

(datetime.timedelta(days=-1),)

[iamshubh95]

As written in doc you shared
year()

Returns the year for the given timestamp. The returned value is in the range -6383 to 9999. If the argument is NULL or empty, the result is also NULL or empty.

year can take any value between -6383 to 9999.

Also when using this query
select extract(year from col1) as year from table1
it returns -1.

My issue is i cant use extract as i dont have control over the sql used.
what i wanted to do is get correct timestamp value instead of getting year as bogus value as 65535.
is that possible using type handler?

[iamshubh95]

just to make my problem more clear
insert another row using this
insert into table1 values(to_timestamp('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))

and then use this type handler-
def timestampAsStr(cursor,name,defaultType,size,precision,scale): if defaultType == cx_Oracle.TIMESTAMP: return cursor.var(cx_Oracle.STRING,100,cursor.arraysize) con.outputtypehandler = timestampAsStr query = 'select * from table1' cursor.execute(query) print cursor.fetchall()
this is returning same value for both row-
('01/01/0001 12:00:00 AM',),('01/01/0001 12:00:00 AM')
and without type handler it is returning
datetime.datetime(65535,1,1,0,0),datetime.datetime(1,1,1,0,0)

[anthony-tuininga]

If I fetch from SQL*Plus I see the following data (based on your inserts):

COL1
---------------------------------------------------------------------------
01-JAN-01 12.00.00.000000 AM
01-JAN-01 12.00.00.000000 AM

And that is the data you get from cx_Oracle as well. So this is not a cx_Oracle issue. You'll need to contact Oracle support regarding this!

[iamshubh95]

Thanks @anthony-tuininga i will ask this issue with oracle support.
Btw could you also tell the logic behind the value getting subtracted from 65536?
how is this value calculated?

[anthony-tuininga]

What version of cx_Oracle are you using? With Python 3.7 and cx_Oracle 7.1 I get the error "ValueError: year -1 is out of range".

[iamshubh95]

python - 2.7.11
cx_oracle - 5.3

[iamshubh95]

Just a update,
running this query -
alter session set nls_timestamp_format='SYYYY-MM-DD HH24:MI:SS'
before my select and then formatting the result with output type handler i got the negative value in string.

[anthony-tuininga]

Glad to hear you found a solution. Please note that Python does not support negative years: https://docs.python.org/3/library/datetime.html. There was a bug in previous versions (aka Python 2.x) that didn't check -- but it was a bug, not a feature. :-)