oracledb does not respect timezone aware timestamps

[shoang22]

Hello,

I'm trying to utilize oracle's TIMESTAMP WITH TIMEZONE to store and query timezone aware timestamps:

import oracledb
from datetime import datetime, timezone


conn = oracledb.connect(
    user="scott",
    password="tiger",
    dsn=oracledb.makedsn("oracle18c", 1521, service_name="xe"),
)


cursor = conn.cursor()

try:
    cursor.execute("drop table datetime_table")
except:
    pass

cursor.execute(
    """
CREATE TABLE datetime_table (
    dt TIMESTAMP,
    dttz TIMESTAMP WITH TIME ZONE
)
"""
)


dt = datetime.now().replace(microsecond=0)
dttz = datetime.now(timezone.utc).astimezone().replace(microsecond=0)


cursor.execute(
    "INSERT INTO datetime_table (dt, dttz) VALUES (:dt, :dttz)",
    {"dt": dt, "dttz": dttz},
)

cursor.execute("SELECT dt, dttz FROM datetime_table")
row = cursor.fetchone()

assert row[0] == dt, f"{row[0]} != {dt}"
assert row[1] == dttz, f"{row[1]} != {dttz}"

Inserts are successful, but when I attempt to query, I don't get timezone information back:

AssertionError: 2024-08-28 09:10:52 != 2024-08-28 09:10:52-04:00

Is this expected behavior?

[zzzeek]

note this came from SQLAlchemy over at sqlalchemy/sqlalchemy#11791 . I assume we'd need to install an outputtypehandler that works with the string value if we wanted to work around this. Seems like the driver should be doing this though

[anthony-tuininga]

This has been a long-standing issue. The C API for Python doesn't support timezone aware dates and trying to match timezones in the database with timezones on the client is difficult, to say the least. I'll take a look at it again and see if there is anything that can be done -- perhaps as an opt-in feature.

[zzzeek]

if you could get oracle DB to return such timestamps as UTC in all cases you wouldn't need to do the "match", this is how it seems to work on PostgreSQL at least

[anthony-tuininga]

Yeah, UTC was designed exactly for that purpose. I'll look into it further.

[rob975]

@anthony-tuininga I recently opened an issue (#373) about timestamps with utc offset (not zone ID). It does not involve timezone databases, so I thought it could be easier to fix.

About timestamps with zone id, python 3.9 added zoneinfo module with a backport for earlier versions. Having that module I believe you could implement the same logic of Java driver which basically maps Oracle zone IDs to IANA zone names.

Personally I dislike zone IDs so it would be nice to have an option (in Defaults class) to just store the offset (see #374).