parseInputDatesAsUTC is not documented

[djmitche]

Per #783, #1746, and a few other TZ-related issues, it seems that node-postgres interprets JS Date objects as in the local timezone, unless pg.defaults.parseInputDatesAsUTC is true.

This behavior is, I think, surprising for those of us building server software, where using UTC everywhere is a general best practice. It's even more surprising since the behavior depends on the local system timezone, so only appears when tests are run on a non-UTC system, meaning tests will pass in CI and fail on development systems.

It would be great if this behavior, and the default, was documented.

[mriedem]

I've been struggling with something related in my app code lately and this would be helpful. I've got some TIMESTAMP fields (WITHOUT TIME ZONE) and I store UTC values (using moment.utc()) but was getting back different offsets in my node app code. A co-worker found this workaround:

https://60devs.com/working-with-postgresql-timestamp-without-timezone-in-node.html

Which I'd like to avoid so it's nice to see this is configurable within node-postgres itself but it's hard to know that if it's not documented.

[mriedem]

This would probably be a good place to mention this:

https://node-postgres.com/features/types

In the date / timestamp / timestamptz section because it does mention:

node-postgres converts DATE and TIMESTAMP columns into the local time of the node process set at process.env.TZ.

Looks like this issue should be moved to the node-postgres-docs repo though because that's where the docs live:

https://github.com/brianc/node-postgres-docs/blob/master/content/features/5-types.mdx

[bbroniewski]

Why library is trying to parse the date to local time of the server, when the column type is just Date, no time.

[djmitche]

Hi @bbroniewski -- I suspect it was unintentional, but language like "why the hell' can read as aggressive and insulting to open-source developers. We can fix bugs while still being kind to each other, and without such language.

And on that topic, it appears that @lolopinto has addressed this bug, although perhaps only for the timestamp type. Hopefully they can provide some detail here: is there more to fix in this issue?

[bbroniewski]

Hi @djmitche sorry for that. I think it is mentioned as to be done in version 4 of types for pg, but it is like this since few years I feel. I see the same issues in 2016.

I did a custom parser like below:

import { types } from 'pg';

types.setTypeParser(types.builtins.DATE, function(val) {
    try{
        return new Date(Date.UTC(parseInt(val.substring(0, 4), 10), parseInt(val.substring(5, 7)), parseInt(val.substring(8, 10)) ));
    } catch {
        return null;
    }
});

[ilibar-zpt]

holy, didn't expect to find a 3yo issue after an hour of stepping through code