timezone aware datetime bind variables are passed as UTC

Empieza ejecutando el script de Python proporcionado contra las versiones de Oracle Database indicadas en los modos thin y thick, centrándote en el manejo de los binds de DB_TYPE_TIMESTAMP_TZ y en la ejecución de update_stmt. Compara el binding, el valor almacenado y la salida de la zona horaria de la sesión. Se considera terminado cuando el comportamiento seleccionado de datetime con zona horaria está implementado de forma coherente sin cambiar inesperadamente el instante o los componentes representados.

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;