Datetime binding issues with timestamp with timezone

[sidhu393]

I could not insert timezone value from datetime.datetime variable even after setting cursor.setinputsizes(oracledb.DB_TYPE_TIMESTAMP_TZ) .

import oracledb

import pytz

 

def main():

   

    db_config={

      "wallet_location"     : "/app/prj/wallet",

      "tns_name"            : "PRJ_PRJ1"

    }

   

    sql_def="CREATE TABLE test_table (test_tmz TIMESTAMP(6) WITH TIME ZONE)"

    query = """

    INSERT INTO test_table test_tmz

    VALUES (:1)

    """

    dt3= datetime.datetime(2028, 3, 10, 10, 15, 30, 220000, datetime.timezone(datetime.timedelta(hours=-2)))

    oracledb.init_oracle_client(config_dir=db_config["wallet_location"])

    with oracledb.connect(dsn=db_config["tns_name"]) as conn:

        with conn.cursor() as cursor:       

            #print("Creating table...")

            #cursor.execute(sql_def)

            print("Inserting data...")

            cursor.setinputsizes(oracledb.DB_TYPE_TIMESTAMP_TZ)  # this is not helping, Inserting dt3 with UTC

            cursor.execute(query, [dt3])

            print("query : ", query)

            print("parms : ", dt3)

            conn.commit()

Workaround I am using

import oracledb

 

def main():

   

    db_config={

      "wallet_location"     : "/app/prj/wallet",

      "tns_name"            : "PRJ_PRJ1"

    }

   

    sql_def="CREATE TABLE test_table (test_tmz TIMESTAMP(6) WITH TIME ZONE)"

    query = """

    INSERT INTO test_table test_tmz

    VALUES (:1)

    """

    dt3= datetime.datetime(2028, 3, 10, 10, 15, 30, 220000, datetime.timezone(datetime.timedelta(hours=-7)))

    dt3_s=dt3.strftime('%d-%b-%Y %H:%M:%S.%f %z')

    oracledb.init_oracle_client(config_dir=db_config["wallet_location"])

    with oracledb.connect(dsn=db_config["tns_name"]) as conn:

        with conn.cursor() as cursor:       

            #print("Creating table...")

            #cursor.execute(sql_def)

            print("Inserting data...")

            # this alter session is to ensure insert string var is correctly converted to TIMESTAMP WITH TIME ZONE by oracle regrdless what defaults has been set in db

            cursor.execute("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-YYYY HH24:MI:SSXFF TZHTZM'") 

            # insert TIMESTAMP WITH TIME ZONE as string

            cursor.execute(query, [dt3_s])

            print("query : ", query)

            print("parms : ", dt3_s)

            conn.commit()