Timezone missing from TIMESTAMP WITH TIME ZONE

[shatteringlass]

1. What versions are you using?

   platform.platform: Linux-5.3.18-24.64-default-x86_64-with-glibc2.3.4
   sys.maxsize > 2**32: True
   platform.python_version: 3.6.13
   oracledb.__version__: 1.4.2
   server: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

2. Is it an error or a hang or a crash? Error: CURRENT_TIMESTAMP is returned as a timezone-unaware datetime

3. What error(s) or behavior you are seeing?

When running SELECT CURRENT_TIMESTAMP FROM DUAL on DBeaver I get a TIMESTAMP WITH TIME ZONE When running the script at the bottom of this report I get:

(datetime.datetime(2023, 12, 20, 18, 33, 28, 585660),) Which is not timezone-aware

5. Does your application call init_oracle_client()?

Yes it does

6. Include a runnable Python script that shows the problem.

import oracledb

oracledb.init_oracle_client()

host = "hostname"
service_name = "servicename"
pw = "dbpass"
user = "username"

connection = oracledb.connect(
    user=user,
    sid=service_name,
    password=pw,
    host=host)

cursor = connection.cursor()
ts = cursor.execute("SELECT CURRENT_TIMESTAMP FROM DUAL").fetchall()
print("Current datetime: "+str(ts))

[anthony-tuininga]

This has been true from the very beginning of cx_Oracle. The primary reason for this is the fact that there are no C APIs for acquiring timezone information, which means that performance would be quite poor when fetching timestamps with time zone information. I've changed this to an enhancement request. I'll add some knob for those interested which will return timezone aware timestamps where applicable.

[rudyryk]

@anthony-tuininga Hi Anthony, if put aside the perfomance issues, are there any workarounds to make oracledb not to lose the timezone information? I've even tried cursor.outputtypehandler with the hope to parse string in application code but seems like information is already lost at that point.

[rudyryk]

The problem is when in the database we have values with different time zones, the returned result would be plain wrong and we can't adjust using some fixed value.

Actual data (pk, ts)

0   2001-01-02 00:00:00.004 +0000
1   2001-01-02 00:00:00.006 +0245

Rows we get with the client:

{'ts': datetime.datetime(2001, 1, 2, 0, 0, 0, 4000), 'pk': 0}
{'ts': datetime.datetime(2001, 1, 2, 0, 0, 0, 6000), 'pk': 1}

[nmoreaud]

When fetching such column, timezone is lost (since there is no performant API to set timezone), but there could be a driver option to return datetimes in UTC or local time, as a workaround.