Node.js Oracle DB date returning wrong value

Question

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

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?

This is a duplicate of github.com/oracle/node-oracledb/issues/1306 Review oracle.github.io/node-oracledb/doc/api.html#datehandling — Christopher Jones
– Christopher Jones, Commented Oct 12, 2020 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
– Jason, Commented Oct 13, 2020 at 6:56
I was just connecting the dots so people can follow the discussion in whatever place it occurs. — Christopher Jones
– Christopher Jones, Commented Oct 13, 2020 at 9:11

memodba · Accepted Answer · 2020-11-17 14:31:16Z

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

answered Nov 17, 2020 at 14:31

memodba

666 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marty131 Over a year ago

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).

Collectives™ on Stack Overflow

Node.js Oracle DB date returning wrong value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related