Timestamp without time zones issue? about node-pg-types

[flesler]

Why was this closed? This issue isn't fixed and without the change I added on my side, I'd be getting wrong timestamps unless the servers timezone is UTC

[flesler]

Here's my temporary hack

var pgjs = require('pg.js');
var oldParser = pgjs.types.getTypeParser(1114);
pgjs.types.setTypeParser(1114, function(str) {
    var date = oldParser(str);
    return new Date( date.getTime() - date.getTimezoneOffset() * 60 * 1000 );
});

[brianc]

Hey @flesler thanks for the detailed explanation. We've had multiple discussions around the issue in the past with node-postgres. The short answer is PostgreSQL specifies if you use a timestamp without timezone then it is expected to be converted into the timezone of the client when it's pulled out & so that's what it does.

The whole purpose of this library is to allow you to override individual type handling to be more in line with what you want in your application, so your approach you put in your 'temporary hack' is actually the way to go. 😄

[flesler]

My (maybe mistaken) understanding of the "without time zone" version was that I'm saving a timestamp and I don't care what was the original time zone when it was saved.
Considering how the library is actually "changing the timestamp" depending on the timezone I find it hard to believe it's the desired behavior.
When would anyone want that?

[flesler]

Are you referring to the following statement from this page?

If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

If so, that paragraph is about input, not output. By either using pgAdmin or JackDB I'm getting the right dates while node-postgres "breaks" the date "epoch-wise".

The docs repeat several times that timestamps with time zone are stored as UTC and converted to local time zone when retrieved but nothing is mentioned about those without.
I think it's fine that the local time zone is set on the date but I seriously think the date shouldn't change epoch-wise (millis since 1970).