node-oracledb
node-oracledb copied to clipboard
oracle
Surprising default session timezone setting
When I execute SELECT SESSIONTIMEZONE FROM dual I get the following results:
- SQLDeveloper:
Europe/Paris. - oracledb driver:
+02:00
It looks like the driver sets the timezone to a fixed value which is the current timezone offset.
As this is summer in Paris, I was getting correct values for summer dates, but a 1hr offset on winter dates. Took me a while to understand what was going on. I was expecting the driver to pick the same default as other tools.
I got rid of all my timezone offset issues by executing ALTER SESSION SET TIME_ZONE='00:00' after connecting.
Comparing with SQL*Plus is a better match, and that gives the same results as node-oracledb for me
Maybe, but as a user I'm expecting local time to take daylight saving time into account. Applying today's offset to all dates is not very meaningful: a meeting that I attended at 4PM in January will show up as 5PM if I look at it now, but will be back at 4PM if I look again in November. Weird!
On the other hand it avoids bugs when computing diffs between dates (one in summer and one in winter).
This strengthens the argument that the date's default should be UTC rather than local because local is too brittle (if you don't enable DST it looks weird to the user, if you enable DST you take the risk of buggy computations).
For now, I have to execute 'ALTER SESSION SET TIME_ZONE='00:00'' before every request. IMO, it's not acceptable.
@larryaubstore Are you unable to set the ORA_SDTZ environment variable?
$ export ORA_SDTZ='UTC'
$ node index.js
Thank you.
May I give you a suggestion ? It is possible to document this in the README.md ?
@larryaubstore The README.md is more of a jumping off point to other pages like INSTALL and doc.
The doc you're looking for is here: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-9163-fetching-date-and-timestamps
node-oracledb 6.0 has changed the behavior of DATE and TIMEZONE types to be more standard. I'll close this old issue; open new ones if there are new questions.