Inserts with TIMESTAMP WITH TIME ZONE always default to GMT

[mkmoisen]

1. What versions are you using?

Oracle 19

platform.platform: Linux-4.18.0-372.76.1.el8_6.x86_64-x86_64-with-glibc2.28 sys.maxsize > 2**32: True platform.python_version: 3.13.0

oracledb.version: 2.4.1

2. Is it an error or a hang or a crash?

Error

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

When inserting a naive or a timezone aware datetime into an oracle TIMESTAMP WITH TIMEZONE column, the result is always saved in GMT.

I'm under the impression that it didn't used to do this.

This bug is happening in both thick and thin mode.

5. Does your application call init_oracle_client()?

The same bug occurs in thinmode and thickmode.

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

CREATE TABLE foo (bar TIMESTAMP WITH TIME ZONE);

import oracledb
oracledb.init_oracle_client()
from datetime import datetime
from zoneinfo import ZoneInfo

conn = oracledb.connect('user/pass@database')
cur = conn.cursor()

naive = datetime(2024, 10, 25, 1, 2, 3)
aware = naive.replace(tzinfo=ZoneInfo('America/Los_Angeles'))

cur.execute('INSERT INTO foo VALUES (:d)', dict(d=naive))
cur.execute('INSERT INTO foo VALUES (:d)', dict(d=aware))

conn.commit()

SELECT * FROM foo

This outputs:

2024-10-25 01:02:03.000000000 GMT
2024-10-25 01:02:03.000000000 GMT

[mkmoisen]

In Oracle, the way it works seems to make intuitive sense:

This is the equivalent of inserting a naive datetime. Oracle will insert it using the database's timezone:

INSERT INTO foo VALUES (timestamp'2024-10-25 01:02:03'); 

This is the equvialent of inserting an aware datetime. Oracle will insert it using the correct timezone:

INSERT INTO foo VALUES (timestamp'2024-10-25 01:02:03 -07:00'); 

---

My python code has always passed naive datetimes, and I've relied on the database timezone to be used.

It seems like it has only recently oracledb has starting inserting GMT instead. And I cannot fix this by specifying a timezone aware datetime.

[anthony-tuininga]

@mkmoisen, python-oracledb (and cx_Oracle before it) has never handled timezone aware datetime instances, mostly due to the fact that there is no C API for doing so. I have given some thought to adding support for it anyway, ensuring that it is opt-in since the performance will be considerably reduced in that case.

Note that when you insert a datetime.datetime instance, the default bind type is DATE, not TIMESTAMP. Try inserting with sysdate vs systimestamp and you will note a difference. Note as well that there is a difference between the database time zone (dbtimezone) and the session time zone (sessiontimezone).

I just tried your code and I see the results coming as expected. Fetching the values back returns the same values (naive, of course) and examining the data in the database shows the session time zone has been applied.

What version of the database and client (in thick mode) are you using?