Node-oracledb: Timestamp problem

[amateur-hkl]

For general questions:

Issuing this command:

select to_timestamp('1-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') HANSEN FROM dual

I get

{"rows":[{"HANSEN":"0000-12-29T22:00:00.000Z"}],"metaData":[{"name":"HANSEN"}]}

Which is not correct

Oracle: 12.1

OS: Ubuntu

Compiler: GCC

Environment variables:

NLS_DATE_FORMAT=YYYY-MM-DD
NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252
NLS_TIMESTAMP_FORMAT=YYYY
NLS_TIMESTAMP_TZ_FORMAT=HH24:MI:SS

I would expect to get back

0001-01-01T00:00:00.000

[dmcghan]

Have you read this? Your session timezone is likely defaulting to UTC +2, so when converting to UTC you get the value you're seeing.

[amateur-hkl]

When setting

export ORA_SDTZ='UTC'

I get

{"rows":[{"HANSEN":"0000-12-30T00:00:00.000Z"}],"metaData":[{"name":"HANSEN"}]}

which is still 24 hours off.

setting absolute difference, like

         export ORA_SDTZ="+26:00"

has no effect

2017-06-16 16:15 GMT+02:00 Dan McGhan [e-mail]:

Have you read this
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-9163-fetching-date-and-timestamps?
Your session timezone is likely defaulting to UTC +2, so when converting to
UTC you get the value you're seeing.

—
You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHub
https://github.com/oracle/node-oracledb/issues/715#issuecomment-309037308,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APhvN6bAzXJQaw7Ym9Iw3jLIiaAQ4rBXks5sEo39gaJpZM4N8gTR
.

[dmcghan]

@amateur-hkl Add sessiontimezone to your query so you can see what's actually being used. It's possible that a trigger is overriding the environment variable.

[amateur-hkl]

Query

select sessiontimezone,
to_timestamp('1-01-01%2000:00:00','YYYY-MM-DD%20HH24:MI:SS') HANSEN FROM
dual"

results in
{"rows":[{"SESSIONTIMEZONE":"UTC","HANSEN":"0000-12-30T00:00:00.000Z"}],"metaData":[{"name":"SESSIONTIMEZONE"},{"name":"HANSEN"}]}

I am more suspicious about year Zero.

2017-06-16 16:48 GMT+02:00 Dan McGhan [e-mail]:

@amateur-hkl https://github.com/amateur-hkl Add sessiontimezone to your
query so you can see what's actually being used. It's possible that a
trigger is overriding the environment variable.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/oracle/node-oracledb/issues/715#issuecomment-309046130,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APhvN8zY7l-lXN42QM6hv9GPRRUS283Jks5sEpXFgaJpZM4N8gTR
.

[amateur-hkl]

Especially since

select sessiontimezone,
to_timestamp('1948-09-23%2000:00:00','YYYY-MM-DD%20HH24:MI:SS')
HANSEN FROM dual

gives

{"rows":[{"SESSIONTIMEZONE":"UTC","HANSEN":"1948-09-23T00:
00:00.000Z"}],"metaData":[{"name":"SESSIONTIMEZONE"},{"name":"HANSEN"}]}

which is correct - except for the trailing Z.

2017-06-16 16:48 GMT+02:00 Dan McGhan [e-mail]:

@amateur-hkl https://github.com/amateur-hkl Add sessiontimezone to your
query so you can see what's actually being used. It's possible that a
trigger is overriding the environment variable.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/oracle/node-oracledb/issues/715#issuecomment-309046130,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APhvN8zY7l-lXN42QM6hv9GPRRUS283Jks5sEpXFgaJpZM4N8gTR
.

[dmcghan]

Why is the trailing Z not correct? Zulu = UTC = GMT (for the most part).

[dmcghan]

Technically, the driver returns dates. You must be doing a JSON.stringify or something similar, which converts the type to a string and does a timezone conversion to Zulu.

[dmcghan]

I believe the problem is related to timezone files and conversions going that far back. When you fetch a datetime data type, the driver treats it like timestamp with local time zone and does conversions. To avoid the conversions, just convert to a string ahead of time. You can do this in your SQL query, using fetchAsString, or fetchInfo.

[amateur-hkl]

Hi Dan

I have tested one date in each year 1512,1612,1712,1812.

Every tested date newer than 1600 is correctly handled; all tested dates
older than 1600 come out incorrectly.

My problem is in testing an general Oracle interface. Rewriting all selects
(including select * from someTable) is no option.

2017-06-16 17:14 GMT+02:00 Dan McGhan [e-mail]:

I believe the problem is related to timezone files and conversions going
that far back. When you fetch a datetime data type, the driver treats it
like timestamp with local time zone and does conversions. To avoid the
conversions, just convert to a string ahead of time. You can do this in
your SQL query, using fetchAsString
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propdbfetchasstring,
or fetchInfo
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propexecfetchinfo
.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/oracle/node-oracledb/issues/715#issuecomment-309053197,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APhvN3dx_7fgMgGXYjnme4P7x7bJrsMpks5sEpvUgaJpZM4N8gTR
.

[dmcghan]

Can you tell us more about the "general Oracle interface"? How do you work with it?

Did you look at fetchAsString or fetchInfo? You would not have to rewrite the selects.

Here's an example work around... I'm using this trigger to set the NLS_DATE_FORMAT:

create or replace trigger hr_logon_trigger
  after logon
  on hr.schema
begin
  execute immediate 'alter session set NLS_DATE_FORMAT=''yyyy-mm-dd"T"HH24:MI:SS"Z"''';
end;

Then I can run this:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');
let conn;
let err;


oracledb.getConnection(config)
  .then((c) => {
    conn = c;

    return conn.execute(
     `select sessiontimezone,
        to_date('0001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') my_date
      from dual`,
      [],
      {
        outFormat: oracledb.OBJECT,
        fetchInfo: {
          MY_DATE: {
            type: oracledb.STRING
          }
        }
      }
    );
  })
  .then(result => {
    console.log(result.rows);
  })
  .catch(err => {
    console.log('Error in processing', err);
  })
  .then(() => {
    if (conn) { // conn assignment worked, need to close
      return conn.close();
    }
  })
  .catch(err => {
    console.log('Error during close', err);
  });

Which gives me:

[ { SESSIONTIMEZONE: 'UTC', MY_DATE: '0001-01-01T00:00:00Z' } ]

The reason I used that specific date format is so that it can be used in a JavaScript Date constructor. Just keep in mind that the time zone of Date instances is always localized to the client’s time zone (derived from the OS):

const myDate = new Date('0001-01-01T00:00:00Z');
console.log(myDate); // Sun Dec 31    0 19:00:00 GMT-0500 (EST)

[bjouhier]

@amateur-hkl Gregorian calendar starts in 1582 or later, depending on country. This may explain why dates before 1582 are handled differently. 10 days were lost in 1582!! See https://en.wikipedia.org/wiki/Gregorian_calendar#Difference_between_Gregorian_and_Julian_calendar_dates

[amateur-hkl]

Hi,

possibly my experience is of interest?

In my experiment I retrieve one connection and keep using that one
connection. Doing this there is no need to autoCommit; we do dedicated
COMMITs or ROLLBACKs.

When doing this I get a "Connection lost (NJS-040) every 62 seconds.

Stack trace:

Uncaught exception Error: NJS-040: connection request timeout

at Pool.onRequestTimeout (.../oracledb/lib/pool.js:123:38)

at Timeout._onTimeout (.../oracledb/lib/pool.js:181:28)

at ontimeout (timers.js:488:11)

at tryOnTimeout (timers.js:323:5)

at Timer.listOnTimeout (timers.js:283:5)

It looks to me that the timeout is not removed when the getConnection call
was successful.

Setting oracledb.queueTimeout=0 helps. But that is not an optimal way of
doing it?

Best regards

amateur_hkl

2017-06-16 18:17 GMT+02:00 Dan McGhan [e-mail]:

Can you tell us more about the "general Oracle interface"? How do you work
with it?

Did you look at fetchAsString
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propdbfetchasstring
or fetchInfo
https://github.com/oracle/node-oracledb/blob/master/doc/api.md#propexecfetchinfo?
You would not have to rewrite the selects.

Here's an example work around... I'm using this trigger to set the
NLS_DATE_FORMAT:

create or replace trigger hr_logon_trigger
after logon
on hr.schemabegin
execute immediate 'alter session set NLS_DATE_FORMAT=''yyyy-mm-dd"T"HH24:MI:SS"Z"''';
end;

Then I can run this:

const oracledb = require('oracledb');const config = require('./dbConfig.js');let conn;let err;

oracledb.getConnection(config)
.then((c) => {
conn = c;

return conn.execute(
 `select sessiontimezone,        to_date('0001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') my_date      from dual`,
  [],
  {
    outFormat: oracledb.OBJECT,
    fetchInfo: {
      MY_DATE: {
        type: oracledb.STRING
      }
    }
  }
);

})
.then(result => {
console.log(result.rows);
})
.catch(err => {
console.log('Error in processing', err);
})
.then(() => {
if (conn) { // conn assignment worked, need to close
return conn.close();
}
})
.catch(err => {
console.log('Error during close', err);
});

Which gives me:

[ { SESSIONTIMEZONE: 'UTC', MY_DATE: '0001-01-01T00:00:00Z' } ]

The reason I used that specific date format is so that it can be used in a
JavaScript Date constructor. Just keep in mind that the time zone of Date
instances is always localized to the client’s time zone (derived from the
OS):

const myDate = new Date('0001-01-01T00:00:00Z');console.log(myDate); // Sun Dec 31 0 19:00:00 GMT-0500 (EST)

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/oracle/node-oracledb/issues/715#issuecomment-309069270,
or mute the thread
https://github.com/notifications/unsubscribe-auth/APhvN_4f4kcxZhytfvyLfpIg0xN_ns2jks5sEqqAgaJpZM4N8gTR
.

[dmcghan]

@amateur-hkl This is the wrong place for your comment. This issue was related to temporal datatypes and has been closed. Could you please open a new issue?