Skip to content
New issue

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

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bad gmt returned on daylight saving time period #2154

Open
g-hawk opened this issue Apr 8, 2020 · 2 comments
Open

bad gmt returned on daylight saving time period #2154

g-hawk opened this issue Apr 8, 2020 · 2 comments

Comments

@g-hawk
Copy link

g-hawk commented Apr 8, 2020

Hi to all,
I have a date type field on a table on a postgresql database (version 10).
I write from Italy and my timezone is GMT+1.
When I read this date and convert it into timestamp, from my node code, I have an error on gmt date setting.
If I store date 02/06/1960, this is returned from node pg code (as select query result) as Thu Jun 02 1960 00:00:00 GMT+0200 (CEST) that is converted into timestamp -302407200000 ( make new Date().getTime() of previous date).
But this timestamp correspond to Wed Jun 01 1960 23:00:00 GMT+01:00 .
On Thu Jun 02 1960 00:00:00 GMT+0200 (CEST) date, js method getTimezoneOffset() return -60 because in 1960 daylight saving time is off in Italy;

If I store date 02/06/1980 (dst on on 1965 in Italy), this is returned from node pg code as Mon Jun 02 1980 00:00:00 GMT+0200 (CEST) that is converted into timestamp 328744800000.
This timestamp correspond to Mon Jun 02 1980 00:00:00 GMT+02:00
On Mon Jun 02 1980 00:00:00 GMT+0200 (CEST) js method getTimezoneOffset() return -120 that is correct (dst on)
Thanks

I think that for 02/06/1960 the correct value to return is Thu Jun 02 1960 00:00:00 GMT+0100 (CET)
Is correct for you? Thanks a lot.
Giuseppe

@g-hawk g-hawk changed the title bad gmt setting bad gmt setting on daylight saving time period Apr 9, 2020
@g-hawk g-hawk changed the title bad gmt setting on daylight saving time period bad gmt returned on daylight saving time period Apr 9, 2020
@boromisp
Copy link
Contributor

boromisp commented Apr 9, 2020

I haven't looked at the specifics of your issue, but some general advice for dealing with dates:

The date type in postgres doesn't have time zone information stored with it.

As far as I know, node-postgres by default converts date values to javascript Date objects in the node.js server's local time zone, which is usually not what you want.

If you would like to interpret the postgres date as midnight in a specific time zone, you could modify your query like this:

SELECT some_date::timestamp AT TIME ZONE 'Europe/Rome';

-- for example:
SELECT '1960-06-02'::date::timestamp AT TIME ZONE 'Europe/Rome';

The resulting timestamp with time zone will have the correct value in UTC.

@abenhamdine
Copy link
Contributor

see also brianc/node-pg-types#50 for more background on parsed dates

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants