Timestamp values differ between oracledb and cx_Oracle

[doerwalter]

Continued from #2

Timestamps fetched by oracledb have different values that those fetched by cx_Oracle. Using alter session set time_zone doesn't make a difference.

Python 3.10.4 (main, Apr 26 2022, 19:42:59) [Clang 13.1.6 (clang-1316.0.21.2)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.3.0 -- An enhanced Interactive Python. Type '?' for help.
🐍 — 1 ❯ odb = oracledb.connect("user/pwd@db")
🐍 — 2 ❯ cdb = cx_Oracle.connect("user/pwd@db")
🐍 — 3 ❯ oc = odb.cursor()
🐍 — 4 ❯ cc = cdb.cursor()
🐍 — 5 ❯ oc.execute("alter session set time_zone='Europe/Berlin'")
🐍 — 6 ❯ cc.execute("alter session set time_zone='Europe/Berlin'")
🐍 — 7 ❯ cc.execute("select start_date from dba_scheduler_jobs where job_name = 'LOG_CLEANUP_JOB'").fetchone()
🐍 — 7 ❮ (datetime.datetime(2019, 3, 26, 4, 33),)
🐍 — 8 ❯ oc.execute("select start_date from dba_scheduler_jobs where job_name = 'LOG_CLEANUP_JOB'").fetchone()
🐍 — 8 ❮ (datetime.datetime(2019, 3, 26, 2, 33),)
🐍 — 9 ❯

The script

import sys, platform, oracledb

print(f"{platform.platform()=}")
print(f"{sys.maxsize > 2**32=}")
print(f"{platform.python_version()=}")
print(f"{oracledb.__version__=}")

gives the following output on my machine:

platform.platform()='macOS-12.4-x86_64-i386-64bit'
sys.maxsize > 2**32=True
platform.python_version()='3.10.4'
oracledb.__version__='1.0.0'

I'm using oracledb in thin mode.

The following Oracle related environment variables are set:

export ORACLE_HOME=~/oracle/instantclient_12_2
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:$ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME
export NLS_LANG=GERMAN_GERMANY.AL32UTF8

[anthony-tuininga]

That SQL supplies no rows in my environment. Can you perhaps create a table with the data type (timestamp with time zone) and populate it with data so that we are using the same data? That would be helpful!

[anthony-tuininga]

This works for me:

create table issue_7 (
    id          number(9) not null,
    date_val    timestamp(6) with time zone
);

insert into issue_7 values (1, 
    to_timestamp_tz('2022-06-04 07:24:36 00:00', 
                    'YYYY-MM-DD HH24:MI:SS TZH:TZM'));

commit;

with this Python code:

import cx_Oracle as oracledb
#import oracledb

DSN = "user/password@connect_string"

conn = oracledb.connect(DSN)
cursor = conn.cursor()
cursor.execute("alter session set time_zone = 'Europe/Berlin'")
for row in cursor.execute("select * from issue_7"):
    print(row)

I get the expected output:

(1, datetime.datetime(2022, 6, 4, 7, 24, 36))

for both cx_Oracle and the new driver (both thick and thin).

[anthony-tuininga]

If I create a value with a timezone other than UTC I do see different results. I'll look into this further and get back to you.

[anthony-tuininga]

I see that GitHub automatically closes this when you mention in your commit comment that this issue is resolved. Please take a look and let me know if you agree. :-)

[doerwalter]

The patch looks reasonable, but I'm not really qualified to judge that.

When I try to follow the installation instructions at https://github.com/oracle/python-oracledb/blob/main/doc/src/user_guide/installation.rst#install-using-github It get:

🐚 ~/checkouts ❯ git clone --recurse-submodules git://opensource.oracle.com/git/oracle/python-oracledb.git
Klone nach 'python-oracledb'...
fatal: Konnte nicht nach opensource.oracle.com verbinden:
opensource.oracle.com[0: 138.1.117.31]: errno=Operation timed out

However when I start with

git clone --recurse-submodules https://github.com/oracle/python-oracledb.git

I get a working oracledb. And the above test does indeed give me the same timestamp as cx_Oracle.

[cjbj]

Thanks for the confirmation. And thanks for the info about opensource.oracle.com. I get the same timeout - and also get it with cx_Oracle. I'll dig into it.

[anthony-tuininga]

This has been included in python-oracledb 1.0.1 which was just released.