rounding error when fetching timestamp

[jogomu]

=== Python and OS ===
3.11.13 (main, Jun 4 2025, 08:57:30) [GCC 13.3.0]
Platform: Linux-6.8.0-55-generic-x86_64-with-glibc2.39
Machine: x86_64
Processor: x86_64

=== Package versions ===
oracledb==3.3.0
pyarrow==21.0.0
pandas==2.3.2
numpy==2.3.3

=== Connecting (thin mode) ===
oracledb.thin: True

DB-side epoch ns: 138502674015079000
Client epoch ns: 138502674015078000
Difference (client - db) ns: -1000

Expected: 138502674015079000
Observed client via DF: 138502674015078000

import os
import sys
import platform


def pkg_ver(name: str) -> str:
    try:
        import importlib
        m = importlib.import_module(name)
        v = getattr(m, "__version__", None) or getattr(m, "version", None)
        if callable(v):
            v = v()
        return f"{name}=={v}"
    except Exception as e:
        return f"{name} import failed: {e!r}"


def print_env():
    print("=== Python and OS ===")
    print(sys.version)
    print("Platform:", platform.platform())
    print("Machine:", platform.machine())
    print("Processor:", platform.processor())

    print("\n=== Package versions ===")
    for p in ["oracledb", "pyarrow", "pandas", "numpy"]:
        print(pkg_ver(p))


def main() -> int:
    print_env()

    # Connection from env with sensible defaults used in our repros
    host = os.getenv("ORACLE_HOST", "oracle")
    port = int(os.getenv("ORACLE_PORT", "1521"))
    service = os.getenv("ORACLE_SERVICE", "FREEPDB1")
    user = os.getenv("ORACLE_USERNAME", "testuser")
    password = os.getenv("ORACLE_PASSWORD", "testpass")

    print("\n=== Connecting (thin mode) ===")
    try:
        import oracledb  # type: ignore
        dsn = oracledb.makedsn(host, port, service_name=service)
        conn = oracledb.connect(user=user, password=password, dsn=dsn)
        print("oracledb.thin:", getattr(oracledb, "is_thin_mode", lambda: None)())
    except Exception as e:
        print("Connection error:", repr(e))
        return 2

    try:
        cur = conn.cursor()
        # Clean slate
        cur.execute(
            """
            BEGIN
              EXECUTE IMMEDIATE 'DROP TABLE T_TSTZ_REPRO';
            EXCEPTION WHEN OTHERS THEN
              IF SQLCODE != -942 THEN RAISE; END IF;
            END;
            """
        )
        # Create and insert precise FF6 value
        cur.execute("CREATE TABLE T_TSTZ_REPRO (a TIMESTAMP(6) WITH TIME ZONE)")
        cur.execute(
            "INSERT INTO T_TSTZ_REPRO(a) VALUES (TO_TIMESTAMP_TZ(:s, 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'))",
            {"s": "1974-05-23 00:57:54.015079 +00:00"},
        )
        conn.commit()

        # DB-side expected epoch ns
        cur.execute(
            """
            SELECT CAST((
              EXTRACT(DAY FROM (CAST(a AT TIME ZONE 'UTC' AS TIMESTAMP) - TIMESTAMP '1970-01-01 00:00:00')) * 86400 +
              EXTRACT(HOUR FROM (CAST(a AT TIME ZONE 'UTC' AS TIMESTAMP) - TIMESTAMP '1970-01-01 00:00:00')) * 3600 +
              EXTRACT(MINUTE FROM (CAST(a AT TIME ZONE 'UTC' AS TIMESTAMP) - TIMESTAMP '1970-01-01 00:00:00')) * 60 +
              EXTRACT(SECOND FROM (CAST(a AT TIME ZONE 'UTC' AS TIMESTAMP) - TIMESTAMP '1970-01-01 00:00:00'))
            ) * 1e9 AS NUMBER(20,0)) AS ns
            FROM T_TSTZ_REPRO
            """
        )
        db_ns = int(cur.fetchone()[0])
        print("\nDB-side epoch ns:", db_ns)

        # DF fetch → Arrow
        import pyarrow as pa  # type: ignore
        import pyarrow.compute as pc  # type: ignore

        client_ns = None
        for odf in conn.fetch_df_batches("SELECT a FROM T_TSTZ_REPRO", size=100):
            tbl = pa.table(odf)
            colname = "A" if "A" in tbl.column_names else "a"
            arr = tbl[colname]
            # Attach UTC if tz metadata is missing (debug only)
            if arr.type.tz is None:
                arr = pc.assume_timezone(arr, "UTC")
            ns_arr = pc.cast(pc.cast(arr, pa.timestamp("ns", tz="UTC")), pa.int64())
            client_ns = int(ns_arr.to_pylist()[0])
            break

        print("Client epoch ns:", client_ns)
        if client_ns is None:
            print("No rows returned from DF fetch")
            return 3

        diff = client_ns - db_ns
        print("Difference (client - db) ns:", diff)
        # Explicitly show the expectation from the report
        print("\nExpected: 138502674015079000")
        print("Observed client via DF:", client_ns)

        # Exit non-zero if mismatch for easy CI detection
        return 0 if client_ns == db_ns else 1
    finally:
        try:
            cur.close()
        except Exception:
            pass
        try:
            conn.close()
        except Exception:
            pass


if __name__ == "__main__":
    sys.exit(main())