Parsing TIMESTAMP WITHOUT TIME ZONE as local time

[skatcat31]

When I get a return of a TIMESTAMP WITHOUT TIME ZONE I get it in local time instead of in UTC like it was stored(hence no time zone). Is there a reason for this or should it be addressed?

[arypurnomoz]

i also got this problem

[jondayft]

See this issue for more info on this #818

[brianc]

There's a reason for this - it comes up about once a year in an issue here and on node-pg-types - basically the tl;dr is we bike shedded on this hard a few years ago & came up with this being the right solution, but the details are in issue #818. (thanks for pointing that out @jondayft!)

The good news is you can override the behavior very easily in node-pg-types to parse dates any way you want, use moment.js to parse them, leave them as strings, anything!

[elhigu]

I actually wasn't able to find any reasons why timestamp without timezone is parsed to local time and not to UTC from #818 (this comes up every now and then in knex issues).

Sounds like it was an old decision maybe because when you pass Date object as an input and then read it with query and parse it back to Date object, the date value remains the same.

Also postgresql spec seems agree that timestamp without timezone is local timestamp

Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.

https://www.postgresql.org/docs/9.2/static/datatype-datetime.html