3

I have a date column in my DB. When querying it with SQLDeveloper like so

SELECT some_date as "Some date",
       cast(some_date as timestamp) at time zone 'UTC' as "Some date UTC"
FROM mytable
FETCH NEXT 1 ROWS ONLY;

I get this back
09-NOV-17 10.00.00.000000000 PM UTC

When running this code in Node:

const oracledb = require('oracledb');

const dbConfig = {
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  connectString: process.env.DB_TNSNAME,
};

oracledb.getConnection(dbConfig).then(async conn => {
  const result = await conn.execute('SELECT some_date FROM mytable FETCH NEXT 1 ROWS ONLY');
  console.log(result.rows[0][0]);
});

I get this output: 2017-11-09T21:00:00.000Z
And when converting this date to local time, for example with moment:

console.log(moment(date).format('YYYY/MM/DD HH:mm:ss'));

I get 2017/11/09 23:00:00

So basically, it seems the date I get when querying the DB through JS is 1 hour behind the date when querying through SQL developer.
I suspect this has something to do with NOV-09 being regular time (so my local time is GMT+2) and the fact that it's currently DST (my local time is GMT+3), but how do I actually fix this?

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
Jason
  • 632
  • 1
  • 5
  • 17
  • This is a duplicate of https://github.com/oracle/node-oracledb/issues/1306 Review https://oracle.github.io/node-oracledb/doc/api.html#datehandling – Christopher Jones Oct 12 '20 at 21:45
  • @Christopher Jones Not a duplicate, as that isn't event Stackoverflow. Also that is my issue that I made after posting this question – Jason Oct 13 '20 at 06:56
  • I was just connecting the dots so people can follow the discussion in whatever place it occurs. – Christopher Jones Oct 13 '20 at 09:11

1 Answers1

2

Did you try this (https://oracle.github.io/node-oracledb/doc/api.html#-16173-fetching-dates-and-timestamps)?

You can set the environment variable ORA_SDTZ before starting Node.js, for example:

$ export ORA_SDTZ='UTC'
$ node myapp.js
memodba
  • 56
  • 6
  • For the record, I followed the 2nd option in memodba's provided link and set the client time zone within my Node.JS app: process.env.ORA_SDTZ = 'UTC'; (before connection is initialised). – Marty131 Oct 04 '21 at 23:27