Date params conversion

[pankleks]

Hi,

I setup node env with TZ=UTC
I run update statement with date params - dates are in UTC ("2016-11-28T08:00:00.000Z")
Still my date is converted to computer local time (i'm in UTC + 1) and I see 9AM in database.
Column type is TIMESTAMP (without timezone)

Does pg respects TZ setting of node env?

Thanks!

[cressie176]

I think you have to set it on your database session.

https://www.postgresql.org/docs/9.1/static/sql-set.html

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

[pankleks]

Thanks, but that's not it - DB is set to UTC.
It's explicitly pg-node that converts dates:(
I would be nice to have someone explain exactly how pg-node is handling this.

[ikokostya]

@pankleks node-postgres uses node-pg-types for parsing postgres types, which internally uses postgres-data for parsing dates.

If column type is timestamp without timezone then value doesn't contain offset (even Z). Thus, postgres-data parses this datetime using javascript Date constructor as local time. You can read more about Date parse behavior in ECMAScript specification https://tc39.github.io/ecma262/#sec-date-time-string-format

When the time zone offset is absent, date-only forms are interpreted as a UTC time and date-time forms are interpreted as a local time.

To resolve this issue you can override default parser for datetime and parse it always in UTC format.

[guikubivan]

To be explicit, do something similar to this post: #1746 (comment)