Timezone Issue When Retrieving a Date Using DB Objects

[Jordan-Lloyd-GW]

1. What versions are you using?

   Oracle database version: 12.1.0.2.0
   process.platform: win32
   process.version: v12.16.1
   process.arch: x64
   require('oracledb').versionString: 5.1.0
   require('oracledb').oracleClientVersionString: 19.5.0.0.0

2. Describe the problem

   I have a PL/SQL stored procedure which has a DATE IN parameter and an object as an OUT parameter (DbObject OUT bind)
   where the OUT parameter type contains a field of DATE type.

   I have set the session timezone on the connection with
   connection.execute(`alter session set time_zone=Europe/London`);

   I create a new Date object in JavaScript of 2021-07-10T12:00:00.000Z which is passed to the IN parameter. This date is
   stored in the DB as 10-JUL-2021 13.00.00 which is correct as it's been converted to the timezone of the DB host. However
   when retrieving it in Node.js from the OUT bind it's returned as 2021-07-10T13:00:00.000Z which I wouldn't expect, and
   seems wrong to me as the date is not being adjusted with respect to the timezone on the connection, causing the date
   retrieved to be a different instant to the one stored.

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

   Node.js
   https://gist.github.com/Jordan-Lloyd-GW/32a96f498cf57f8f8b39048433ab17e2

   PL/SQL
   https://gist.github.com/Jordan-Lloyd-GW/dc067bd6eeca3b6897aea8bfaf1142e2

[cjbj]

I recall an Oracle client 'feature' for dates in objects - they don't respect the session date format. You could confirm with SQL*Plus. There didn't seem to be any appetite for the backward incompatible changes that would be needed to make a change, though I could imagine that more complex solutions involving different ALTER SESSION keywords and environment variable names etc might be possible. My search-fu is failing and I can't immediately pull up references.

[stale]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[charleaux]

I believe for these issues we just set the timezone on the servers with a variable that exists before the node process starts.

ORA_SDTZ

[stale]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not been updated for a month.