Date fields are incorrectly converted, due to timezones

[mxriverlynn]

System Info

OSX 10.11.6, node v4.2.5, npm 2.14.12, using Oracle 11g

I have verified this issue against oracledb v1.9.3 and v1.10.1 - have not tried other versions, but I would expect it to behave the same.

Description Of The Problem

I have a table with a date field in it. When certain dates are inserted into the table and then selected via the oracledb library, they are converted incorrectly and end up being the previous day.

For example, I can reliably reproduce the problem with a date of 01-JAN-2016 in my table.

Selecting this row from the database using SQLDeveloper returns the correct date.

However, selecting this row with the oracledb library returns Thu Dec 31 2015 23:00:00 GMT-0600 (CST)

This is not correct. It should return January 1st 2016, not December 31st, 2015.

The problem is caused by time zone interpretation.

If I add 6 hours to the date column when selecting it (to offset my timezone), it comes back as Jan 1, 2016, as expected.

Steps To Reproduce

Create a table with a single Date column

CREATE TABLE "SYSTEM"."TEST_DATE" ( "D" DATE )

Insert a single row into that table

INSERT INTO "SYSTEM"."TEST_DATE" (D) VALUES ('01-JAN-2016'); COMMIT;

Ensure your local system date / time is set to Central Daylight Time (either Waco, TX or Chicago, IL)

Run this node / oracledb code to see the date time parsed incorrectly:

var oracledb = require('oracledb');

var config = {
  user          : "someone",
  password      : "super secret",
  connectString : "TNSNAME"
};

oracledb.getConnection(config, function(err, connection) {
  if (err) { throw err; }

  var sql = "SELECT D from TEST_DATE";
  connection.execute(sql, function(err, result) {
    if (err) { throw err; }

    console.log(result.rows);
  });
});

To see the date correctly parsed, adjust the select statement to this:

  var sql = "SELECT D + .25 from TEST_DATE";

Temporary Workaround

Until the date is parsed correctly, accounting for the timezones, we are adding 6 hours (D + .25) to our select statements. This is good enough for now, but makes me pretty darn nervous. With cpu clock drift and other factors, I don't have confidence that this workaround will hold permanently.

[dmcghan]

@derickbailey What do you get from running the following query?

select dbtimezone, sessiontimezone 
from dual;

[mxriverlynn]

@dmcghan from SQLDeveloper, I get:

DBTIME
------
SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00 
America/Chicago   

running from the same code example above, i get

'+00:00', '-05:00'

same data - just different representation, i think

[dmcghan]

I think that makes sense... As mentioned here in the doc, the driver is treating dates as TIMESTAMP WITH LOCAL TIME ZONE. This data type has some automatic timezone conversions associated with it.

When you inserted the date, did you use SQL Developer? If so, that client doesn't treat dates the same as the driver. So if you insert with SQL Developer (or any client that doesn't do the conversions) and then pull it out with the driver, you can end up with this kind of issue.

Be careful with your current workaround as it doesn't take daylight savings time into account.

One solution is to set your session timezone to GMT/+00:00 before executing your query. This would effectively disable the conversions. Another option is to fetch the data as a string which also prevents conversions.

Dates are tricky. I've been meaning to write a post on this topic to shine some light on the subject. This is some good motivation! :)

[mxriverlynn]

timezones are terrible, as a developer. they do nothing but cause headaches like this - especially in JavaScript, since there is no separate of Date and Time.

my expectation of a Date data type is not to have a time or timezone associated with it.

is oracle storing the timezone, when the data type is set to Date?

Insert

It doesn't matter if i insert the row w/ the oracledb lib or via sqldeveloper. The result is the same, when selecting it, because the data stored in oracle is still 01-JAN-2016, no matter which client inserts the data.

I first ran into this problem with data that was inserted via the oracledb library. After confirming the issue in my code, I created the steps to reproduce by manually inserting the row. The result is the same.

fetchAsString

Using fetchAsString seems like a reasonable workaround for now, but not a long-term solution to the problem.

I can write this code against the above table / row:

var oracledb = require('oracledb');

var config = {
  user          : "...",
  password      : "...",
  connectString : "..."
};

// fetch as string
oracledb.fetchAsString = [oracledb.DATE];

oracledb.getConnection(config, function(err, connection) {
  if (err) { throw err; }

  var sql = "SELECT D from TEST_DATE";
  connection.execute(sql, function(err, result) {
    if (err) { throw err; }

    // convert the string to a date
    var d = new Date(result.rows[0][0]);

    console.log(d);
  });
});

In this scenario, the date is converted correctly and I see Fri Jan 01 2016 00:00:00 GMT-0600 (CST) in the resulting date object.

But having to know / remember to do this is a big gotcha. It puts the onus on the developer to know how dates are treated and converted by this library.

It breaks expectations.

Suggestion: Default To Better Date Conversion

A better default behavior - rather than treating the date as if it had a timezone attached to it - would be to use this "fetch as string, then convert" technique, within the oracledb lib.

I don't expect this will cover all use cases, but it seems to be a more sensible default.

Allowing someone to change how the conversion from the oracle data type to the JS data type would be a good option for handling other scenarios.

[cjbj]

@derickbailey date handling in node-oracledb is on the list to be reviewed. https://github.com/oracle/node-oracledb/issues/20 is still open for this.

[nalbion]

I have read suggestions to set NLS_LANG and NLS_DATE_FORMAT, but NLS_LANG can't be set on a connection - its an environment variable. ...on the DB server or client? If the latter, AWS does not allow for environment variables to be set.

[mxriverlynn]

@nalbion can you adjust the command used to start your application?

for example, when I need to set an env var for a specific process, I adjust my node command to include the var before calling node.

i do this frequently when testing my "production" vs "development" configuration:

NODE_ENV=production node my-app.js

this will cause node to see the NODE_ENV environment variable is set to "production", when running.

[nalbion]

@derickbailey no, because it's not really an application in the conventional sense - it's a Lambda function.

I've resorted to:

SELECT 
  TO_CHAR(CREATE_DATE, 'YYYY-MM-DD') CREATE_DATE
  FROM MY_TABLE;

[cjbj]

@nalbion also see https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propdbfetchasstring

[dmcghan]

@derickbailey I finally got the post out: https://jsao.io/2016/09/working-with-dates-using-the-nodejs-driver/

I hope that helps clarify how node-oracledb works with dates...

[tmeisenh]

So the tl;dr is treat dates coming in and out as strings to avoid any liberties taken by the driver?

[dmcghan]

The tl;dr is to set ORA_SDTZ correctly (the time zone you're storing dates in).

There are many ways to do this, the simplest is to just set the environment variable before starting the Node.js app:

$ export ORA_SDTZ='America/New_York'
$ node server.js 

If you're in a situation similar to @nalbion (where AWS doesn't allow setting env variables), consider using a trigger instead.

create or replace trigger my_logon_trigger
  after logon
  on hr.schema
begin
  execute immediate 'alter session set time_zone=''America/New_York''';
end;

[cjbj]

The 1.12.0-dev doc links to Dan's article, and I've since borrowed the summary and added it directly to api.md for the next release.