timezone aware datetime bind variables are passed as UTC

[rob975]

When using timezone aware datetime bind variables, the value passed to the server has the same date/time components as the bind variable but the time zone is changed to UTC regardless of session time zone.

I'm aware that named time zones are not supported, but that change messes up data. imho proper behaviors could be:

1. passing UTC offset as TZ info (instead of original named time zone)
2. pass UTC as TZ info but with date/time components adjusted accordingly

I have tested version 2.3.0 of the driver, both thin and thick modes, with 12.2 and 19.13 databases. Running below script in thin mode with a 19.13 database, produces next output

platform.platform: Linux-6.9.12-200.fc40.x86_64-x86_64-with-glibc2.39
sys.maxsize > 2**32: True
platform.python_version: 3.9.19
oracledb.version: 2.3.0
database version: 19.13.0.0.0

Session time zone: -04:00

Stored old value: 2024-08-04T12:11:13.123-04:00
Parsed old value: 2024-08-04T12:11:13.123

Binding variable: 2024-08-04T13:24:45.272-04:00

Stored new value: 2024-08-04T13:24:45.272+00:00
Parsed new value: 2024-08-04T13:24:45.272

Python script that shows the problem

from datetime import datetime, timezone
from os import environ
from platform import platform, python_version
from sys import maxsize

from oracledb import (
    DB_TYPE_TIMESTAMP_TZ,
    clientversion,
    connect,
    init_oracle_client,
    version,
)

# read connect info and mode from environment variables
thick_mode = environ.get("DB_THICK_MODE", "false").lower() == "true"
dsn = environ.get("DB_DSN", "localhost:1521/orcl")
user = environ.get("DB_USER", "scott")
password = environ.get("DB_PASSWORD", "tiger")

# sql statements
session_stmt = r"select sessiontimezone from dual"
select_stmt = r"""select to_char(test_ts, 'yyyy-mm-dd"T"hh24:mi:ss.fftzh:tzm'),
test_ts from test_tz where test_id = 1"""
update_stmt = r"update test_tz set test_ts = :dt where test_id = 1"

print("platform.platform:", platform())
print("sys.maxsize > 2**32:", maxsize > 2**32)
print("platform.python_version:", python_version())
print("oracledb.version:", version)

if thick_mode:
    init_oracle_client()
    print("client version:", ".".join(str(n) for n in clientversion()))

with connect(dsn=dsn, user=user, password=password) as conn:
    print("database version:", conn.version)

    with conn.cursor() as cursor:
        # get session time zone
        cursor.execute(session_stmt)
        print("\nSession time zone:", cursor.fetchone()[0])

        # read old value
        cursor.execute(select_stmt)
        result = cursor.fetchone()
        print("\nStored old value:", result[0])
        print("Parsed old value:", result[1].isoformat(timespec="milliseconds"))

        # update with current date truncated to milliseconds
        dt = datetime.now(timezone.utc).astimezone()
        dt = dt.replace(microsecond=dt.microsecond // 1000 * 1000)
        print("\nBinding variable:", dt.isoformat(timespec="milliseconds"))
        cursor.prepare(update_stmt)
        cursor.setinputsizes(dt=DB_TYPE_TIMESTAMP_TZ)
        cursor.execute(None, dt=dt)

        # read new value
        cursor.execute(select_stmt)
        result = cursor.fetchone()
        print("\nStored new value:", result[0])
        print("Parsed new value:", result[1].isoformat(timespec="milliseconds"))

SQL needed to create the database schema

create table test_tz (
  test_id number(19),
  test_ts timestamp(3) with time zone,
  primary key(test_id)
);
insert into test_tz values (1, timestamp '2024-08-04 12:11:13.123');
commit;