Oracle column with DATE datatype loses time part

[romiof]

What language are you using?

Using with Python

What version are you using?

0.3.1

What database are you using?

Oracle 12c

What dataframe are you using?

PolaRS with PyArrow

Can you describe your bug?

I've a lot of tabels in my Oracle database, which have columns with DATE datatype.

This datatype in Oracle, used to have DATE or DATE+TIME data.

When selected this columns, CX convert it to DATE and I lose the time part of the column.

What are the steps to reproduce the behavior?

Selecting SYSDATE from Oracle is enough to reproduce it.

import connectorx as cx

ENGINE_PATH = oracle+oracledb://user:MyP4ssword@oraclesrv01.mycompany.com:1526/MyServiceName
query = "SELECT SYSDATE FROM dual"

cx.read_sql(ENGINE_PATH, query, return_type="arrow")
# also happen with return_type="polars" or "pandas"

pyarrow.Table
COL1: date32[day]
----
COL1: [[2023-03-08]]

If possible, please include a minimal simple example including:

Database setup if the error only happens on specific data or data type

Any table with DATE datatype.

Other thoughts

If I use CAST(foo AS TIMESTAMP) I can preserve the time part when load in CX.

But I'm planning to do a Extract-Load of a LOT of tables, and write all columns in my query of all tables, will be to much verbose.

[romiof]

@ alexander.beedie in PolaRS' Discord, helped with it:

Yup; looking at their source code I'm afraid it looks like DATE => Date (which makes sense by name, but obviously Oracle's DATE is more of a TIMESTAMP with no fractional seconds, so...)

impl_transport!(
    name = OracleArrow2Transport,
    error = OracleArrow2TransportError,
    systems = OracleTypeSystem => Arrow2TypeSystem,
    route = OracleSource => Arrow2Destination,
    mappings = {
        { NumFloat[f64]                 => Float64[f64]                 | conversion auto }
        { Float[f64]                    => Float64[f64]                 | conversion none }
        { BinaryFloat[f64]              => Float64[f64]                 | conversion none }
        { BinaryDouble[f64]             => Float64[f64]                 | conversion none }
        { NumInt[i64]                   => Int64[i64]                   | conversion auto }
        { Blob[Vec<u8>]                 => LargeBinary[Vec<u8>]         | conversion auto }
        { Clob[String]                  => LargeUtf8[String]            | conversion none }
        { VarChar[String]               => LargeUtf8[String]            | conversion auto }
        { Char[String]                  => LargeUtf8[String]            | conversion none }
        { NVarChar[String]              => LargeUtf8[String]            | conversion none }
        { NChar[String]                 => LargeUtf8[String]            | conversion none }
        { Date[NaiveDate]               => Date32[NaiveDate]            | conversion auto }
        { Timestamp[NaiveDateTime]      => Date64[NaiveDateTime]        | conversion auto }
        { TimestampTz[DateTime<Utc>]    => DateTimeTz[DateTime<Utc>]    | conversion auto }
    }
);

[wangxiaoying]

Thanks @romiof for opening the issue. It should be fixed by #473 thanks to @FerriLuli !