Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Date corruption #85

Closed
thaumant opened this Issue Oct 4, 2013 · 3 comments

Comments

Projects
None yet
3 participants

thaumant commented Oct 4, 2013

When I insert a date, I get correct value in DB. But when I select a date from DB, I get a modified date: looks like it ignores JS timezone ore something like that.

Test

var ora = require('oracle'),
    config = require('./config');

var con;
ora.connect(config.db, function(err, connection) {
    con = connection;
    con.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'", [], showTz);
});

function showTz() {
    console.log('# 1. SHOW TIMEZONES:');
    var jsTz = (0 - new Date().getTimezoneOffset()) / 60;
    jsTz = (jsTz < 0 ? String(jsTz) : '+' + jsTz) + ':00';
    con.execute('select sessiontimezone tz from dual', [], function(err, result) {
        var oraTz = result[0]['TZ'];
        console.log('- JS timezone: ', jsTz);
        console.log('- Oracle timezone: ', oraTz);
        showOraDate();
    });
}

function showOraDate() {
    console.log('\n# 2. GET SYSDATE AS DATE AND AS STRING FROM DB:');
    con.execute("select sysdate asdate, to_char(sysdate) asstr from dual", [],
        function(err, result) {
            var res1 = result[0].ASDATE, type1 = res1 instanceof Date ? 'date' : 'wtf',
                res2 = result[0].ASSTR, type2 = typeof res2 === 'string' ? 'string' : 'wtf';
            console.log('- sysdate as ' + type1 + ': ', res1);
            console.log('- sysdate as ' + type2 + ': ', res2);
            showJsDate();
        });
}

function showJsDate() {
    console.log('\n# 3. SAVE JS DATE AND GET IT DB:');
    var date = new Date();

    con.execute("select :1 asdate, to_char(:1) asstr from dual", [date],
        function(err, result) {
            var res1 = result[0].ASDATE, type1 = res1 instanceof Date ? 'date' : 'wtf',
                res2 = result[0].ASSTR, type2 = typeof res2 === 'string' ? 'string' : 'wtf';
            console.log('- original date: ', date);
            console.log('- saved date as ' + type1 + ': ', res1);
            console.log('- saved date as ' + type2 + ': ', res2);
            con.close();
        });
}

Output

#1. SHOW TIMEZONES:
- JS timezone:  +4:00
- Oracle timezone:  +04:00

#2. GET SYSDATE AS DATE AND AS STRING FROM DB:
- sysdate as date:  Fri Oct 04 2013 21:28:12 GMT+0400 (MSK)
- sysdate as string:  2013-10-04 17:28:12

#3. SAVE JS DATE AND GET IT DB:
- original date:  Fri Oct 04 2013 17:24:32 GMT+0400 (MSK)
- saved date as date:  Fri Oct 04 2013 21:24:32 GMT+0400 (MSK)
- saved date as string:  2013-10-04 17:24:32

There is some mistiming between DB host and my workstation (16:43 vs 16:47), though it is unimportant. The issue is in the difference between inserting and selecting a date: when I simply save a date, retrieve it and save again, I get corrupted data.

Collaborator

raztus commented Oct 7, 2013

I don't have much time at the moment to look at your specific examples, but I assume this is the same issue debated at #29. The point: the Oracle DATE and TIMESTAMP types cannot store a timezone, but Javascript dates must store a timezone. In an attempt at consistency, we translate the Oracle date to a Javascript date by using the setUTC____ methods. Thus, the only "safe" (consistent) Javascript date methods on the returned date are the getUTC____ methods. See the discussion at #29 for more.

Note that currently the Oracle type TIMESTAMP WITH TIMEZONE is currently treated as a simple TIMESTAMP. There is an open issue for this at #67, which I've not yet had time to tackle.

So, I think the real issue is that we need better documentation around how dates are handled.

Contributor

bjouhier commented Dec 19, 2013

I ran into a similar problem: dates do not rountrip properly. This is due to an inconsistency in the API used for setters and getters: https://github.com/joeferner/node-oracle/blob/master/src/executeBaton.cpp#L64-69 vs. https://github.com/joeferner/node-oracle/blob/master/src/connection.cpp#L507-513

I fixed the it by adding UTC to the getters.

Collaborator

raztus commented Dec 24, 2013

Fixed in commit 92fcf9b

@raztus raztus closed this Dec 24, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment