python-oracledb this client incorrect database timestamp #131

dec04 · 2023-01-30T11:35:27Z

dec04
Jan 30, 2023

When i use thin client python-oracledb to get data from tables, wich contains datetime columns with timestamp as "2022-10-15 14:35:09.000000 +00:00" i receive wrong timestamp like this "2022-10-12 17:39:09", time difference of 3 days and 4 minutes. But when i use cx_oracle version 8.3.0 i get correct timestamp from same table and same column.

python-oracledb vesion: 1.2.2

cx_oracle version: 8.3.0

oracle database version: 19.14.0.0.0

conn: Connection = oracledb.connect(user=user1,
                                    password=password1,
                                    dsn=connection1,
                                    encoding=codepage1)

conn_cx_ora = cx_Oracle.connect(user=user1,
                                password=password1,
                                dsn=connection1,
                                encoding=codepage1)

try:
    cursor: Cursor = conn.cursor()
    cursor.execute(sql)
    rows1 = cursor.fetchall()
    mig_start1: datetime = rows1[1][1]

    cursor: Cursor = conn_cx_ora.cursor()
    cursor.execute(sql)
    rows_cx = cursor.fetchall()
    mig_start_cx: datetime = rows_cx[1][1]
finally:
    cursor.close()
    conn.close()
    conn_cx_ora.close()

print(rows1)
print(rows_cx)

output:

[datetime.datetime(2022, 10, 12, 8, 2, 43)]
[datetime.datetime(2022, 10, 15, 4, 58, 33]

I need understand, why python-oracledb receive wrong datetime.

anthony-tuininga · 2023-01-30T15:55:53Z

anthony-tuininga
Jan 30, 2023
Maintainer

Can you supply a script that creates the table and populates it with a single row that demonstrates the failure? I suspect a bug of some sort in processing the data sent by the server. Before asking you for more complicated things I'd like to see if I can replicate the issue myself!

6 replies

anthony-tuininga Jan 31, 2023
Maintainer

Yes, I understand that the problem is during the fetch. I want to know the data that is being fetched, though! I don't see the same results as you do -- so I need the create table and insert statements so that I can have the same data in my database as you do in yours!

cjbj Jan 31, 2023
Maintainer

@dec04 for completeness please also let us know:

what operating system you are using
whether you have any locale or time related environment set, e.g the TZ or ORA_SDTZ environment variables
whether you see the issue with python-oracledb Thick mode (which would be unlikely, but let us know)

dec04 Feb 1, 2023
Author

Machines software:
Client PC:

OS: Windows 10 Corporate LTSC. Version: 10.0.17763, 17763
python 3.9
python-oracledb 1.2.2
Path variables: NLS_LANG = AMERICAN_AMERICA.AL32UTF8

Server DB:

LSB Version: :core-4.1-amd64:core-4.1-noarch
Version Oracle DB: 19.03
ORACLE_SID=*
ORACLE_HOME_LISTNER=/data/oracle/orahome/distr
ORACLE_HOME=/data/oracle/orahome/distr

Create table:

CREATE TABLE FOR_TEST
(
  BATCH_ID   NUMBER        NOT NULL
    CONSTRAINT FOR_TEST_BATCH_ID_PK
      PRIMARY KEY,
  BATCH_NAME VARCHAR2(100) NOT NULL,
  MIG_START  TIMESTAMP(6) WITH TIME ZONE,
  MIG_END    TIMESTAMP(6) WITH TIME ZONE
)
  ORGANIZATION INDEX
/

Row inser with one procedure in pl/sql. This is part of procedure code:

INSERT INTO MIG.FOR_TEST (BATCH_ID, BATCH_NAME, MIG_START, MIG_END)
  VALUES (1000,
          'BATCH_4_TEST',
          (SELECT systimestamp FROM DUAL),
          NULL);

INSERT INTO MIG.FOR_TEST (BATCH_ID, BATCH_NAME, MIG_START, MIG_END)
  VALUES (1001,
          'BATCH_4_TEST_2',
          (SELECT min(SCHEDULE_DATE) FROM ANOTHER_TABLE),
          NULL);

In this two examples for inserting data, in first example i insert systimestamp. Witch fetch correct. Is second insert, i fetch data from another table, and data look like this 2020-11-06 11:12:56 without microseconds and timezone information.

SELECT * FROM MIG.FOR_TEST;

Data in database look loke this:

But, as a say early data wich fetch from python-oracledb look different, only for data, received from another table and without information about timezone and microsecconds:

Python code:

user2: str = 'USER'
password2: str = 'PASSWORD'
connection2: str = 'CONNECTION_STRING'
codepage2: str = 'UTF8'

sql = 'SELECT * FROM MIG.FOR_TEST'

fsom_conn_cx_ora: Connection = oracledb.connect(user=user2,
                                                password=password2,
                                                dsn=connection2,
                                                encoding=codepage2)

try:
    cursor: Cursor = fsom_conn_cx_ora.cursor()
    cursor.execute(sql)
    rows_cx = cursor.fetchall()

finally:
    cursor.close()
    fsom_conn_cx_ora.close()

print(rows_cx)

Python output:

0 = {tuple} (1000, 'BATCH_4_TEST', datetime.datetime(2023, 2, 1, 9, 55, 6, 991495), None)
1 = {tuple} (1001, 'BATCH_4_TEST_2', datetime.datetime(2020, 11, 3, 14, 16, 56), None)

Row wich start at 1 index have not correct date as you see. Difference is -3 days, and 4 hours (3:04:00).

anthony-tuininga Feb 2, 2023
Maintainer

I have just pushed a commit to main that may address the issue. If you are able to build from source you can see if that resolves your issue. If it does, please let me know! If not, can you please supply a script that creates a table and populates it using static data? You can use the to_timestamp_tz() to ensure that the values are stored with the correct timezone in the database.

dec04 Feb 2, 2023
Author

What script are you talking about? About the one that populates SHCEDULE_DATE in my previous example? Or about a new script that fills in data using to_timestamp_tz(), can you describe in more detail. I'm sorry for my english, it's not verry good.

cjbj · 2023-02-02T06:21:40Z

cjbj
Feb 2, 2023
Maintainer

Can you try your current code first?

1 reply

dec04 Feb 2, 2023
Author

with buid from source code library, correct?

cjbj · 2023-02-02T06:42:13Z

cjbj
Feb 2, 2023
Maintainer

Yes. See Anthony's post for the link to the instructions

1 reply

dec04 Feb 2, 2023
Author

ok, i'l try new build from souce version:

py -3 -m pip show oracledb    
                                                                                                                                                                                                                                                                       
Name: oracledb
Version: 1.3.0b1
Summary: Python interface to Oracle Database
Home-page: https://oracle.github.io/python-oracledb
Author: Anthony Tuininga
Author-email: anthony.tuininga@oracle.com
License: Apache and/or UPL
Location: d:\library\programs\python 3.9\lib\site-packages\oracledb-1.3.0b1-py3.9-win-amd64.egg
Requires: cryptography
Required-by:

Run same code, it's not working.

Data in tables

Output:

"D:\Library\Programs\Python 3.9\python.exe" D:\PyCharmProjects\****\for_test.py
 
[
    (1000, 'BATCH_4_TEST', datetime.datetime(2023, 2, 1, 9, 55, 6, 991495), None), 
    (1001, 'BATCH_4_TEST_2', datetime.datetime(2020, 11, 3, 14, 16, 56), None)
]

dec04 · 2023-02-02T08:51:00Z

dec04
Feb 2, 2023
Author

Ok, i'l try insert random date in table. This script to fill data:

CREATE TABLE FOR_TEST
(
  BATCH_ID      NUMBER        NOT NULL
    CONSTRAINT FOR_TEST_BATCH_ID_PK
      PRIMARY KEY,
  BATCH_NAME    VARCHAR2(100) NOT NULL,
  SCHEDULE_DATE TIMESTAMP(6) WITH TIME ZONE,
  MIG_START     TIMESTAMP(6) WITH TIME ZONE,
  MIG_END       TIMESTAMP(6) WITH TIME ZONE
)
  ORGANIZATION INDEX
/

CREATE SEQUENCE FOR_TEST_SEQ
  START WITH 2000
  INCREMENT BY 1;

INSERT INTO MIG.FOR_TEST
SELECT FOR_TEST_SEQ.NEXTVAL                                                 AS BATCHID,
       'batch' || DBMS_RANDOM.STRING('U', TRUNC(DBMS_RANDOM.VALUE(1, 10))) AS BATCH_NAME,
       TO_TIMESTAMP(SYSDATE) + (DBMS_RANDOM.VALUE(0, 186400) / 86400)       AS SCHEDULE_DATE,
       CURRENT_TIMESTAMP +
       NUMTODSINTERVAL(DBMS_RANDOM.VALUE(1, 24 * 60 * 60), 'second')        AS MIG_START,
       NULL                                                                 AS MIG_END
  FROM DUAL
  CONNECT BY LEVEL <= 50;

SELECT * FROM MIG.FOR_TEST;

Now, i use early python script to fetch data, this is output :

(3105, 'batch_NUOO', datetime.datetime(2023, 1, 31, 3, 38, 34), datetime.datetime(2023, 1, 30, 17, 11, 15, 789610), None)
(3106, 'batch_SEATAU', datetime.datetime(2023, 1, 30, 18, 40, 52), datetime.datetime(2023, 1, 30, 11, 38, 59, 158398), None)
(3107, 'batch_IIBBFWW', datetime.datetime(2023, 1, 30, 16, 15, 8), datetime.datetime(2023, 1, 30, 19, 7, 26, 363551), None)
(3108, 'batch_X', datetime.datetime(2023, 1, 31, 1, 56, 25), datetime.datetime(2023, 1, 30, 18, 14, 32, 497015), None)
(3109, 'batch_FCRXBVL', datetime.datetime(2023, 1, 30, 6, 32, 49), datetime.datetime(2023, 1, 31, 9, 21, 5, 375904), None)
(3110, 'batch_KUKDA', datetime.datetime(2023, 1, 31, 12, 43, 29), datetime.datetime(2023, 1, 31, 7, 45, 43, 948412), None)
(3111, 'batch_AIAB', datetime.datetime(2023, 1, 31, 13, 25, 34), datetime.datetime(2023, 1, 30, 23, 9, 50, 437856), None)
(3112, 'batch_VVMATX', datetime.datetime(2023, 1, 31, 21, 49, 19), datetime.datetime(2023, 1, 31, 6, 51, 44, 159577), None)
(3113, 'batch_XUVGJXET', datetime.datetime(2023, 1, 30, 8, 22, 14), datetime.datetime(2023, 1, 30, 22, 19, 22, 466494), None)
(3114, 'batch_FN', datetime.datetime(2023, 1, 31, 15, 38, 18), datetime.datetime(2023, 1, 31, 2, 26, 15, 434777), None)
(3115, 'batch_JYMY', datetime.datetime(2023, 1, 30, 10, 46, 6), datetime.datetime(2023, 1, 31, 7, 1, 34, 711922), None)
(3116, 'batch_MJBPJXSJI', datetime.datetime(2023, 1, 31, 8, 16, 30), datetime.datetime(2023, 1, 30, 17, 21, 15, 215999), None)
(3117, 'batch_JOAD', datetime.datetime(2023, 1, 30, 9, 42, 49), datetime.datetime(2023, 1, 31, 6, 19, 12, 169254), None)
(3118, 'batch_HF', datetime.datetime(2023, 1, 30, 12, 48, 23), datetime.datetime(2023, 1, 30, 17, 8, 32, 279497), None)
(3119, 'batch_EK', datetime.datetime(2023, 1, 31, 5, 34, 1), datetime.datetime(2023, 1, 31, 4, 12, 4, 161156), None)
(3120, 'batch_Q', datetime.datetime(2023, 1, 31, 16, 35, 49), datetime.datetime(2023, 1, 30, 20, 0, 12, 194808), None)
(3121, 'batch_VLP', datetime.datetime(2023, 1, 30, 16, 37, 10), datetime.datetime(2023, 1, 31, 2, 24, 42, 336425), None)
(3122, 'batch_BRROPC', datetime.datetime(2023, 1, 31, 17, 12, 46), datetime.datetime(2023, 1, 31, 5, 12, 57, 107783), None)
(3123, 'batch_DDUDI', datetime.datetime(2023, 1, 30, 5, 9, 5), datetime.datetime(2023, 1, 30, 14, 5, 15, 484685), None)
(3124, 'batch_TOHKNL', datetime.datetime(2023, 1, 31, 16, 20, 1), datetime.datetime(2023, 1, 31, 1, 19, 0, 454082), None)
(3125, 'batch_RZQE', datetime.datetime(2023, 1, 31, 9, 38, 14), datetime.datetime(2023, 1, 30, 15, 53, 18, 66280), None)
(3126, 'batch_MTXHYZQH', datetime.datetime(2023, 1, 31, 6, 8, 10), datetime.datetime(2023, 1, 30, 18, 46, 48, 21517), None)
(3127, 'batch_KRVAAKSA', datetime.datetime(2023, 1, 31, 19, 57, 47), datetime.datetime(2023, 1, 30, 12, 49, 10, 478113), None)
(3128, 'batch_QBJ', datetime.datetime(2023, 1, 31, 7, 34, 17), datetime.datetime(2023, 1, 30, 17, 36, 16, 941753), None)
(3129, 'batch_IXG', datetime.datetime(2023, 1, 31, 10, 11, 27), datetime.datetime(2023, 1, 31, 8, 29, 58, 282827), None)
(3130, 'batch_KGATYHW', datetime.datetime(2023, 1, 30, 8, 30, 22), datetime.datetime(2023, 1, 31, 6, 28, 9, 949998), None)
(3131, 'batch_GS', datetime.datetime(2023, 1, 31, 9, 56, 3), datetime.datetime(2023, 1, 31, 7, 8, 44, 721823), None)
(3132, 'batch_ALFW', datetime.datetime(2023, 1, 31, 21, 9, 41), datetime.datetime(2023, 1, 31, 8, 23, 17, 412944), None)
(3133, 'batch_HDOGWXXK', datetime.datetime(2023, 2, 1, 5, 56, 40), datetime.datetime(2023, 1, 30, 17, 37, 17, 143241), None)
(3134, 'batch_RPZIHV', datetime.datetime(2023, 1, 31, 20, 16, 28), datetime.datetime(2023, 1, 31, 11, 26, 28, 228584), None)
(3135, 'batch_AFX', datetime.datetime(2023, 1, 31, 16, 20, 35), datetime.datetime(2023, 1, 31, 0, 55, 29, 389936), None)
(3136, 'batch_MFSDKEV', datetime.datetime(2023, 2, 1, 5, 35, 10), datetime.datetime(2023, 1, 30, 23, 55, 44, 791108), None)
(3137, 'batch_RWRWJLWV', datetime.datetime(2023, 1, 31, 15, 19, 10), datetime.datetime(2023, 1, 31, 9, 22, 47, 246294), None)
(3138, 'batch_E', datetime.datetime(2023, 1, 31, 20, 30, 52), datetime.datetime(2023, 1, 30, 21, 47, 22, 857842), None)
(3139, 'batch_WBTNW', datetime.datetime(2023, 1, 31, 21, 55, 17), datetime.datetime(2023, 1, 30, 12, 41, 4, 854328), None)
(3140, 'batch_PBIOV', datetime.datetime(2023, 1, 30, 14, 47, 47), datetime.datetime(2023, 1, 30, 15, 27, 26, 715095), None)
(3141, 'batch_GVZDEF', datetime.datetime(2023, 2, 1, 0, 29, 34), datetime.datetime(2023, 1, 30, 15, 20, 46, 977682), None)
(3142, 'batch_DZXJCAC', datetime.datetime(2023, 1, 31, 18, 53, 13), datetime.datetime(2023, 1, 31, 8, 44, 0, 803645), None)
(3143, 'batch_ARAZMSEGR', datetime.datetime(2023, 1, 31, 9, 13, 27), datetime.datetime(2023, 1, 31, 1, 51, 14, 11294), None)
(3144, 'batch_CNXFI', datetime.datetime(2023, 1, 30, 4, 35, 47), datetime.datetime(2023, 1, 30, 18, 50, 44, 234419), None)
(3145, 'batch_PESLFYB', datetime.datetime(2023, 1, 31, 6, 21, 37), datetime.datetime(2023, 1, 30, 20, 31, 48, 334213), None)
(3146, 'batch_HPMGDBYAG', datetime.datetime(2023, 1, 31, 20, 5, 5), datetime.datetime(2023, 1, 31, 5, 30, 32, 285549), None)
(3147, 'batch_IGGAFGKK', datetime.datetime(2023, 1, 31, 16, 41, 3), datetime.datetime(2023, 1, 31, 3, 39, 6, 482986), None)
(3148, 'batch_PKGPCMWQL', datetime.datetime(2023, 1, 31, 21, 58, 7), datetime.datetime(2023, 1, 30, 14, 48, 2, 881996), None)
(3149, 'batch_SMBNDACW', datetime.datetime(2023, 1, 31, 11, 29, 17), datetime.datetime(2023, 1, 31, 10, 9, 33, 602145), None)
(3150, 'batch_FYRPIUFI', datetime.datetime(2023, 1, 31, 6, 34, 23), datetime.datetime(2023, 1, 31, 3, 37, 14, 494402), None)
(3151, 'batch_BIPXOLBY', datetime.datetime(2023, 1, 31, 8, 53, 24), datetime.datetime(2023, 1, 31, 0, 34, 17, 377892), None)
(3152, 'batch_HUEFIGAR', datetime.datetime(2023, 1, 31, 4, 51, 8), datetime.datetime(2023, 1, 30, 15, 39, 15, 433013), None)
(3153, 'batch_BVYXR', datetime.datetime(2023, 1, 30, 12, 23, 26), datetime.datetime(2023, 1, 30, 15, 22, 44, 63852), None)
(3154, 'batch_QF', datetime.datetime(2023, 1, 30, 12, 6, 26), datetime.datetime(2023, 1, 30, 19, 33, 5, 846982), None)

All data incorrect, difference about 3 days and 4 hours. If i count correctly

10 replies

cjbj Feb 4, 2023
Maintainer

@dec04 this is just to check we understand what your problem is. Once we know, we can propose a patch to python-oracledb for handling named time zones.

dec04 Feb 6, 2023
Author

Good morning, i try your solution and it work.

alter session set time_zone = '+00:00';

INSERT INTO MIG.FOR_TEST
SELECT FOR_TEST_SEQ.NEXTVAL                                                 AS BATCHID,
       'batch' || DBMS_RANDOM.STRING('U', TRUNC(DBMS_RANDOM.VALUE(1, 10))) AS BATCH_NAME,
       TO_TIMESTAMP(SYSDATE) + (DBMS_RANDOM.VALUE(0, 186400) / 86400)       AS SCHEDULE_DATE,
       CURRENT_TIMESTAMP +
       NUMTODSINTERVAL(DBMS_RANDOM.VALUE(1, 24 * 60 * 60), 'second')        AS MIG_START,
       NULL                                                                 AS MIG_END
FROM DUAL
CONNECT BY LEVEL <= 10;

Data in tables:

Python code stay same, but i check only one column mig_start in this example:

sql2 = 'select batch_name, mig_start from mig.for_test'

fsom_conn_cx_ora = oracledb.connect(user = user1,
                                    password = password1,
                                    dsn = connection1,
                                    encoding = codepage)

try:
    cursor = fsom_conn_cx_ora.cursor()
    cursor.execute(sql2)
    rows_cx = cursor.fetchall()

finally:
    cursor.close()
    fsom_conn_cx_ora.close()

print(rows_cx)

Python output:

[
 ('batchRGUAP', datetime.datetime(2023, 2, 7, 6, 1, 0, 207571)),
 ('batchU', datetime.datetime(2023, 2, 6, 21, 25, 38, 361393)),
 ('batchDYOWRE', datetime.datetime(2023, 2, 6, 22, 42, 33, 324716)),
 ('batchRSXDBZXD', datetime.datetime(2023, 2, 7, 0, 53, 13, 534623)),
 ('batchX', datetime.datetime(2023, 2, 7, 2, 46, 41, 425864)),
 ('batchHFSUOR', datetime.datetime(2023, 2, 7, 5, 50, 14, 236979)),
 ('batchDPDJQCP', datetime.datetime(2023, 2, 7, 4, 10, 38, 687833)),
 ('batchVHTY', datetime.datetime(2023, 2, 6, 21, 53, 15, 443978)),
 ('batchBSAMGKE', datetime.datetime(2023, 2, 6, 21, 59, 55, 11801)),
 ('batchWYR', datetime.datetime(2023, 2, 7, 1, 43, 2, 100694))
]

anthony-tuininga Feb 6, 2023
Maintainer

Thanks for confirming. We will discuss and get back to you on any proposed solutions.

anthony-tuininga Mar 31, 2023
Maintainer

Version 1.3.0 (which was just released) raises an exception in thin mode when it detects a named time zone is being used instead of returning invalid data. If you would like to see support for this added, please create an enhancement request!

dec04 Apr 6, 2023
Author

@anthony-tuininga thanks for answer, i create a enchancement request

python-oracledb this client incorrect database timestamp #131

Uh oh!

Uh oh!

dec04 Jan 30, 2023

Replies: 4 comments · 18 replies

Uh oh!

anthony-tuininga Jan 30, 2023 Maintainer

Uh oh!

anthony-tuininga Jan 31, 2023 Maintainer

Uh oh!

cjbj Jan 31, 2023 Maintainer

Uh oh!

dec04 Feb 1, 2023 Author

Uh oh!

anthony-tuininga Feb 2, 2023 Maintainer

Uh oh!

dec04 Feb 2, 2023 Author

Uh oh!

cjbj Feb 2, 2023 Maintainer

Uh oh!

dec04 Feb 2, 2023 Author

Uh oh!

cjbj Feb 2, 2023 Maintainer

Uh oh!

dec04 Feb 2, 2023 Author

Uh oh!

dec04 Feb 2, 2023 Author

Uh oh!

cjbj Feb 4, 2023 Maintainer

Uh oh!

Uh oh!

dec04 Feb 6, 2023 Author

Uh oh!

Uh oh!

anthony-tuininga Feb 6, 2023 Maintainer

Uh oh!

anthony-tuininga Mar 31, 2023 Maintainer

Uh oh!

dec04 Apr 6, 2023 Author

dec04
Jan 30, 2023

Replies: 4 comments 18 replies

anthony-tuininga
Jan 30, 2023
Maintainer

anthony-tuininga Jan 31, 2023
Maintainer

cjbj Jan 31, 2023
Maintainer

dec04 Feb 1, 2023
Author

anthony-tuininga Feb 2, 2023
Maintainer

dec04 Feb 2, 2023
Author

cjbj
Feb 2, 2023
Maintainer

dec04 Feb 2, 2023
Author

cjbj
Feb 2, 2023
Maintainer

dec04 Feb 2, 2023
Author

dec04
Feb 2, 2023
Author

cjbj Feb 4, 2023
Maintainer

dec04 Feb 6, 2023
Author

anthony-tuininga Feb 6, 2023
Maintainer

anthony-tuininga Mar 31, 2023
Maintainer

dec04 Apr 6, 2023
Author