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 notifications@github.com:

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 notifications@github.com:

@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 notifications@github.com:

@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 notifications@github.com:

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 notifications@github.com:

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?