Wrong date saving when object used

[yakov-rs]

1. What versions are you using? database: 18c

   process.platform - win32 process.version - v14.16.0 process.arch - x64 require('oracledb').versionString - 5.5.0 require('oracledb').oracleClientVersionString - 19.14.0.0.0

2. Is it an error or a hang or a crash? error

3. What error(s) or behavior you are seeing?

wrong time in date

4. Include a runnable Node.js script that shows the problem.

`

process.env.TZ='Europe/Moscow';
process.env.ORA_SDTZ='Europe/Moscow';
const oracledb = require('oracledb');

async function execute() {
    let connection;

    let opts = { autoCommit: true };
    let value = '2022-11-17T18:47:40+03:00';
    let result;

    try {
        connection = await oracledb.getConnection({
            user: "YOUR_USERNAME",
            password: 'YOUR_PASSWORD',
            connectString: "YOUR_CONNECTION_STRING"
        });

        await connection.execute("insert into test_save_date_from_node(dt, source) values (:dt, 'insert')",
            {
                dt: {
                    val: new Date(value),
                    type: oracledb.DATE,
                    dir: oracledb.BIND_IN
                }
            },
            opts);

        const ClassName = await connection.getDbObjectClass('TEST_SAVE_DATE_FROM_NODE_REC');
        await connection.execute("begin test_save_date_from_node_prc(p_rec => :p_rec); end;",
            {
                p_rec: {
                    val: { DT: new Date(value) },
                    type: ClassName,
                    dir: oracledb.BIND_IN
                }
            },
            opts);

        result = await connection.execute("SELECT dt FROM test_save_date_from_node");
        result.rows.forEach(item => console.log(new Date(item).toLocaleString()));

    } catch (err) {
        console.error(err);
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
    }

}

execute();

`

In database:

`

create table test_save_date_from_node(
  dt date,
  source varchar2(10 char)
);

create type test_save_date_from_node_rec is object (
  dt date
);

create or replace procedure test_save_date_from_node_prc(
  p_rec test_save_date_from_node_rec
)
as
begin
  insert into test_save_date_from_node(dt, source) values (p_rec.dt, 'type');
end;

`

result:

`

17.11.2022 18:47:40 insert
17.11.2022 15:47:40 type

`

`

SELECT DBTIMEZONE FROM DUAL;

Europe/Moscow

`

Please help me to understand, it's error or I did something wrong?

[abrar-khan007]

Try date using interface

[yakov-rs]

@abrar-khan007 Could you be more specific and show me how can I do it?

[cjbj]

See https://github.com/oracle/node-oracledb/issues/1340#issuecomment-777345315

[yakov-rs]

@cjbj ok, txs If I create type like this:

create type test_save_date_from_node_rec is object ( dt timestamp with local time zone );

and other code would stay as it was before (type of field in table - date), then result will be correct.

@cjbj tell me please can you see that it could be correct workaround?

[abrar-khan007]

@abrar-khan007 Could you be more specific and show me how can I do it?

Have u checked for null , I believe default format is getting executed . Using interface provide value be specific for conversation