search

xdenser / node-firebird-libfbclient

Firebird SQL binding
MIT License
82 stars 34 forks source link

Same result set producing different dates (Node / IBExpert) #105

Closed mreis1 closed 4 years ago

mreis1 commented 4 years ago

Hello guys

Recently came across the following issue:

image

As you can see, I'm my system is using a GMT-4 timezone.

In the picture you can see that node.js is correctly aligned with my windows timezone. It's 15:30 (GMT-4) in both.

Now , here comes the weird part. The same query is returning completely different dates.

Instead of having 2020-06-26 we've 2020-07-20

How is this possible? Any help would be really appreciated.

👍

xdenser commented 4 years ago

Hm.. JS has zero based month. There is tests - but it converts from JS to Db and back - and this works... I wonder why nobody noticed that before?

mreis1 commented 4 years ago

I don't know. Honestly it's the first time I see this incoherence.

mreis1 commented 4 years ago

@xdenser Could it be related with the timezone? Because using european timezones (Lisbon, Paris, ...) I'm not able to reproduce this issue.

xdenser commented 4 years ago

But timezone is not stored in DB. What about Date and Time as separate fields?

mreis1 commented 4 years ago

So I created a test table with the following data and started playing with timezones to confirm my theory. Things get messy when UTC is negative.

INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (1, '2020-01-01 10:59:59', '2020-01-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (2, '2020-02-01 10:59:59', '2020-02-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (3, '2020-03-01 10:59:59', '2020-03-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (4, '2020-04-01 10:59:59', '2020-04-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (5, '2020-05-01 10:59:59', '2020-05-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (6, '2020-06-01 10:59:59', '2020-06-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (7, '2020-07-01 10:59:59', '2020-07-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (8, '2020-08-01 10:59:59', '2020-08-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (9, '2020-09-01 10:59:59', '2020-09-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (10, '2020-10-01 10:59:59', '2020-10-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (11, '2020-11-01 10:59:59', '2020-11-01', '10:59:59');
INSERT INTO TEST_TABLE (ID, TS, D, T) VALUES (12, '2020-12-01 10:59:59', '2020-12-01', '10:59:59');

COMMIT WORK;

I did a pretty print for TS, D and T (Timestamp, date, time accordingly)

Test result for GMT-4


[ { ID: 1,
    TS: 2020-01-01T14:59:59.000Z,
    D: 2020-01-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-01-2020',
    TS2: '01-01-2020 10:59:59' },
  { ID: 2,
    TS: 2020-03-01T14:59:59.000Z,
    D: 2020-03-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-03-2020',
    TS2: '01-03-2020 10:59:59' },
  { ID: 3,
    TS: 2020-03-01T14:59:59.000Z,
    D: 2020-03-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-03-2020',
    TS2: '01-03-2020 10:59:59' },
  { ID: 4,
    TS: 2020-05-01T14:59:59.000Z,
    D: 2020-05-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-05-2020',
    TS2: '01-05-2020 10:59:59' },
  { ID: 5,
    TS: 2020-05-01T14:59:59.000Z,
    D: 2020-05-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-05-2020',
    TS2: '01-05-2020 10:59:59' },
  { ID: 6,
    TS: 2020-07-01T14:59:59.000Z,
    D: 2020-07-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-07-2020',
    TS2: '01-07-2020 10:59:59' },
  { ID: 7,
    TS: 2020-07-01T14:59:59.000Z,
    D: 2020-07-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-07-2020',
    TS2: '01-07-2020 10:59:59' },
  { ID: 8,
    TS: 2020-08-01T14:59:59.000Z,
    D: 2020-08-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-08-2020',
    TS2: '01-08-2020 10:59:59' },
  { ID: 9,
    TS: 2020-10-01T14:59:59.000Z,
    D: 2020-10-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-10-2020',
    TS2: '01-10-2020 10:59:59' },
  { ID: 10,
    TS: 2020-10-01T14:59:59.000Z,
    D: 2020-10-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-10-2020',
    TS2: '01-10-2020 10:59:59' },
  { ID: 11,
    TS: 2020-12-01T14:59:59.000Z,
    D: 2020-12-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-12-2020',
    TS2: '01-12-2020 10:59:59' },
  { ID: 12,
    TS: 2020-12-01T14:59:59.000Z,
    D: 2020-12-01T04:00:00.000Z,
    T: 1899-12-31T15:32:35.417Z,
    T2: '10:59:59',
    D2: '01-12-2020',
    TS2: '01-12-2020 10:59:59' } ]

Test result for GMT-1 (Azores)

[ { ID: 1,
    TS: 2020-01-01T11:59:59.000Z,
    D: 2020-01-01T01:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-01-2020',
    TS2: '01-01-2020 10:59:59' },
  { ID: 2,
    TS: 2020-03-01T11:59:59.000Z,
    D: 2020-03-01T01:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-03-2020',
    TS2: '01-03-2020 10:59:59' },
  { ID: 3,
    TS: 2020-03-01T11:59:59.000Z,
    D: 2020-03-01T01:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-03-2020',
    TS2: '01-03-2020 10:59:59' },
  { ID: 4,
    TS: 2020-05-01T10:59:59.000Z,
    D: 2020-05-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-05-2020',
    TS2: '01-05-2020 10:59:59' },
  { ID: 5,
    TS: 2020-05-01T10:59:59.000Z,
    D: 2020-05-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-05-2020',
    TS2: '01-05-2020 10:59:59' },
  { ID: 6,
    TS: 2020-07-01T10:59:59.000Z,
    D: 2020-07-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-07-2020',
    TS2: '01-07-2020 10:59:59' },
  { ID: 7,
    TS: 2020-07-01T10:59:59.000Z,
    D: 2020-07-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-07-2020',
    TS2: '01-07-2020 10:59:59' },
  { ID: 8,
    TS: 2020-08-01T10:59:59.000Z,
    D: 2020-08-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-08-2020',
    TS2: '01-08-2020 10:59:59' },
  { ID: 9,
    TS: 2020-10-01T10:59:59.000Z,
    D: 2020-10-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-10-2020',
    TS2: '01-10-2020 10:59:59' },
  { ID: 10,
    TS: 2020-10-01T10:59:59.000Z,
    D: 2020-10-01T00:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-10-2020',
    TS2: '01-10-2020 10:59:59' },
  { ID: 11,
    TS: 2020-12-01T11:59:59.000Z,
    D: 2020-12-01T01:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-12-2020',
    TS2: '01-12-2020 10:59:59' },
  { ID: 12,
    TS: 2020-12-01T11:59:59.000Z,
    D: 2020-12-01T01:00:00.000Z,
    T: 1899-12-31T12:54:31.139Z,
    T2: '10:59:59',
    D2: '01-12-2020',
    TS2: '01-12-2020 10:59:59' } ]
mreis1 commented 4 years ago

Paris Timezone, same result set.

[ { ID: 1,
    TS: 2020-01-01T09:59:59.000Z,
    D: 2019-12-31T23:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-01-2020',
    TS2: '01-01-2020 10:59:59' },
  { ID: 2,
    TS: 2020-02-01T09:59:59.000Z,
    D: 2020-01-31T23:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-02-2020',
    TS2: '01-02-2020 10:59:59' },
  { ID: 3,
    TS: 2020-03-01T09:59:59.000Z,
    D: 2020-02-29T23:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-03-2020',
    TS2: '01-03-2020 10:59:59' },
  { ID: 4,
    TS: 2020-04-01T08:59:59.000Z,
    D: 2020-03-31T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-04-2020',
    TS2: '01-04-2020 10:59:59' },
  { ID: 5,
    TS: 2020-05-01T08:59:59.000Z,
    D: 2020-04-30T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-05-2020',
    TS2: '01-05-2020 10:59:59' },
  { ID: 6,
    TS: 2020-06-01T08:59:59.000Z,
    D: 2020-05-31T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-06-2020',
    TS2: '01-06-2020 10:59:59' },
  { ID: 7,
    TS: 2020-07-01T08:59:59.000Z,
    D: 2020-06-30T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-07-2020',
    TS2: '01-07-2020 10:59:59' },
  { ID: 8,
    TS: 2020-08-01T08:59:59.000Z,
    D: 2020-07-31T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-08-2020',
    TS2: '01-08-2020 10:59:59' },
  { ID: 9,
    TS: 2020-09-01T08:59:59.000Z,
    D: 2020-08-31T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-09-2020',
    TS2: '01-09-2020 10:59:59' },
  { ID: 10,
    TS: 2020-10-01T08:59:59.000Z,
    D: 2020-09-30T22:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-10-2020',
    TS2: '01-10-2020 10:59:59' },
  { ID: 11,
    TS: 2020-11-01T09:59:59.000Z,
    D: 2020-10-31T23:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-11-2020',
    TS2: '01-11-2020 10:59:59' },
  { ID: 12,
    TS: 2020-12-01T09:59:59.000Z,
    D: 2020-11-30T23:00:00.000Z,
    T: 1899-12-31T10:50:38.038Z,
    T2: '10:59:59',
    D2: '01-12-2020',
    TS2: '01-12-2020 10:59:59' } ]
xdenser commented 4 years ago

hmm probably I do not understand how dates in JS work

var date = new Date(0);
date.setFullYear(2020);
date.setMonth(5);
date.setDate(18);

console.log(date);

outputs 2020-07-18T23:00:00.000Z What?

And this is not only in Node (V8), but in firefox the same effect not only in GMT-4

mreis1 commented 4 years ago

Surprisingly, neither do I. At least I never came across this issue before.

After some research setYear() is not recommend.

So I tried to run your example in a GMT-1 (Such as Azores) and I got a similar result. But If you construct the date using new Date(YYYY,MM,DD) the issue disappears.

> var date = new Date(0);
undefined
> date.setFullYear(2020);
1609459200000
> date.setMonth(5);
1593644400000
> date.setDate(18);
1595113200000
>
> console.log(date);
2020-07-18T23:00:00.000Z

> 
> new Date(2020,5,18)
2020-06-18T00:00:00.000Z
>
mreis1 commented 4 years ago

@xdenser , there also this:

new Date(new Date(new Date(new Date(0).setFullYear(2020)).setMonth(5)).setDate(10)).toString() 'Fri Jul 10 2020 23:00:00 GMT+0000 (GMT)'

new Date(new Date(new Date(new Date().setFullYear(2020)).setMonth(5)).setDate(10)).toString() 'Wed Jun 10 2020 13:39:55 GMT+0000 (GMT)'

PS: Just tested your latest commit and it looks like the issue is gone. Ty So, my conclusion is: Date(0) and GMT-X = unpredictable behaviour.