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 #1340 (comment)

[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