Provide option to interpret dates as UTC

[nareshbhatia]

Looking at https://github.com/brianc/node-postgres/issues/510, it appears that dates used to be interpreted as UTC, but then there was a decision made to interpret them as system time. In my experience interpreting dates and timestamps in system timezone is very fragile. Even having to set the system clock to UTC is not always possible. It's an even bigger problem when the application needs to handle multiple timezones.

The rule of thumb we use is to always use UTC internally. Only the user interface layer deals with local timezone and converts it to UTC on input and local timezone on output. This allows the app to be extremely portable - no need to worry about system time or local timezone of the client machine.

I was wondering if there is a way to provide an option to interpret dates as UTC or system timezone. This will make sure that backward compatibility is maintained but make the library flexible to cater for different use cases.

[lroal]

I agree with @nareshbhatia, dates / timestamps should not worry about system time or local timezone of the client machine.

[lroal]

Hmm..thinking more about it...if we have a date with timezone, it really doesn't matter if date object is utc or system timezone. But with for date without timezone, it could be reasonable to have it in utc. There should be an option though.

[brianc]

If you'd like to provide the ability to provide an option in a pull request I'll happily take a look at it. The current suggested fix is to implement your own custom date/time parser and register it with pg-types.

[bendrucker]

The correct way to handle dates is in the system timezone. That's the behavior of the Date constructor.

I think the best solution here would be a separate package rather than an option:

require('node-postgres-utc')(require('node-postgres').types)

Then override the parser there.

[brianc]

+1 for that, absolutely.

On Wed, Jun 10, 2015 at 1:10 PM, Ben Drucker [email protected] wrote:

The correct way to handle dates is in the system timezone. That's the behavior of the Date constructor.

I think the best solution here would be a separate package rather than an option:

require('node-postgres-utc')(require('node-postgres').types)

Then override the parser there.

— Reply to this email directly or view it on GitHub https://github.com/brianc/node-postgres/issues/783#issuecomment-110838352 .

[abenhamdine]

Hmmm... just bumped into the same problem. Any new on this issue ? I'm surprised it's wasn't already solved because it seems pretty probable that everyone run into this...

[teburd]

I just ran in to this the other day and ended up casting my date column types to text just to avoid the weird situation of postgres dates getting converted to js Date objects, where they have a bunch of added and incorrect information about time and timezone, especially when serializing these value to json.

My vote for how node-postgres deals with dates is just treat them as plain strings when decoding them. Its safer than the alternative, and easy enough to convert the plain strings to Date objects if desired.

[luggage66]

I think I am having a related issue. I am storing date (no time) in postgres and am getting a different date in ~~windows~~. Edit: This was a previous version of pg-types, actually. 1.8.0 changed the behavior with: https://github.com/brianc/node-pg-types/commit/468dfda8b51efaa155a8f47d4a1649d8d7328e9c

// pg-types 1.8+
client.query("SELECT '2016-01-01'::date as date", ...); // Thu Dec 31 2015 19:00:00 GMT-0500 (Eastern Standard Time)

//  pg-types 1.7
client.query("SELECT '2016-01-01'::date as date", ...); // Fri Jan 1 2016 00:00:00 GMT-0500 (EST)

[spollack]

i just spent some time digging into this, since we use the postgres date column type in our schema, and our code treats all dates and times as UTC.

In our test and production environments, everything works great, since the machines are running in UTC. However, locally, where our machines are in various timezones, you get rather unexpected results. For example, running on my machine in PST:

var testDate = new Date('2016-02-22T00:00:00.000Z');
var resultDate = db.query("select $1::date as result", [testDate], _).rows[0].result;
console.log(testDate.toString()); // "Sun Feb 21 2016 16:00:00 GMT-0800 (PST)"
console.log(resultDate.toString()); // "Sat Feb 20 2016 16:00:00 GMT-0800 (PST)"

So, round-tripping a javascript Date in and out of a postgres date can give you a different result (the wrong day, i.e. a different number of milliseconds since the epoch).

The specific problem here has to do with the translation of input data that happens in pg's prepareValue function. (not the output data translation via pg-types.)

I'm going to make a fix locally, but @brianc if you are open to it i can open a PR to make this behavior configurable. I.e. optionally parse input javascript Dates as UTC instead of as local time with offset in prepareValue. Please let me know your thoughts.

[jonahbron]

This is a good option, it really needs to be configurable. As of now, it cannot be changed :-1: .

[guikubivan]

Setting that default did not work for, but this did in case anybody else is lost: To be explicit, do something similar to this post: https://github.com/brianc/node-postgres/issues/1746#issuecomment-435678889

[jlaustill]

I'm not sure if this issue is exactly the right one, but I fixed this by doing this

pg.types.setTypeParser(1082, (text: string): string => text.split("T")[0]);

and then we are converting the results to Temporal.PlainDate from there to completely erase the timezone isssues with dates. We store them in Postgres as dates, and it's lovely to be able to treat them as dates in code now that Temporal has hit stage 3! Once we have all of the dates converted, I'll be updating that parser to just return a PlainDate, then we'll be living the dream.

[charmander]

@jlaustill Why the split? The simple date type shouldn’t contain a T. But otherwise, yes, that (setting a no-op text parser of String or x => x for date) is the recommended approach for now – parsing dates into JavaScript Dates is a historical pg mistake that’s kept for backward compatibility.

[jlaustill]

@jlaustill Why the split? The simple date type shouldn’t contain a T. But otherwise, yes, that (setting a no-op text parser of String or x => x for date) is the recommended approach for now – parsing dates into JavaScript Dates is a historical pg mistake that’s kept for backward compatibility.

In my testing, the postgres Date columns are already parsed by new Date and then toISOString(or something, I only see the result and haven't dug in) before they get to this parser, so the strings coming in are for example "2020-01-01" in the db shows up in this parser as "2020-01-01T00:00:00.000Z" so the split is just to turn it back into "2020-01-01" so PlainDate can parse it, because PlainDate will throw an exception if you pass it "2020-01-01T00:00:00.000Z". I was pretty surprised when I console logged text from this parse and saw the timestamps, but I'm also not an expert on this code base. I've used it for years without digging in this deep. But we have shipped some MAJOR bugs because of this lately so I'm digging in now. I hope this helps explain my comment a bit better. I think I'll do up a demo repo today to share as well, it will be a good exercise to know that I'm getting things right and something else in our codebase isn't causing differences.

[jlaustill]

I am unable to duplicate this in a minimal example, so I think there must be something in our codebase I need to find. So disregard my comments.