Node-postgres: parseInputDatesAsUTC in defaults.js

[peterw8102]

I'm storing time stamps in my database without a timezone and want to treat all dates in this way, displaying the correct timezone in the UI.

I can parse dates from the database by setting a handler with 'db_types.setTypeParser'

In #943 @spollack submitted a change to add a flag to defaults.js (parseInputDatesAsUTC) to fix this issue #783

While this works, there's no way to actually change this flag from its default (false) except by editing defaults.js in the code. I'd assumed I could over-ride this in my configurations or env but actually that's not possible.

I'm happy to look at addressing this but want to check that I'm not missing something obvious in the code that get me around this.

[peterw8102]

OK - ignore that :/ - just change the value in pg.defaults - seems a bit crude though.

[charmander]

I'm storing time stamps in my database without a timezone

Don’t; just store them with a UTC timezone.

[andrewackerman]

@charmander My DB server was migrated from a MySQL DB where the datetimes were stored as DATETIME, and the migration script created all the columns as time stamps without timezones. This wasn't an issue previously, as all datetimes were implicitly treated as UTC anyway, but now the library is automatically converting every datetime string I pass in from the local timezone to UTC - even though it's passed in as an ISO8601 formatted string that is explicitly stating that it is a UTC timestamp. This is not acceptable behavior, and there needs to be an option to disable it without having to resort to such a clunky workaround.

[charmander]

@andrewackerman pg doesn’t do anything to strings. Anyway, details in #1746.

[AlecZadikian9001]

I think the handling of timestamps is still strange. If I'm inserting something with the ::timestamp cast, it ends up putting it in as local time then strips the offset. I'd expect it to know I'm casting as timestamp and use UTC. I know it knows the cast because it complains about not knowing the type if I don't cast to anything and just SELECT it.

Btw, I don't ever want to use timestamptz in my database; using timezones anywhere except the end user interface doesn't make sense, and I question why Postgres even has that feature.

[boromisp]

The default timestamp and date handling in node-postgres (or it's dependencies) is kind of broken and depends on the timezone configuration of the host machine since these types cannot be cleanly mapped to the JavaScript Date object.

However.

The Postgres type designed for UTC timestamps is the timestamptz. The timestamp type does not encode a specific point in time but a date-and-time pair that could be in any timezone.

If you really want to store UTC timestamps in the timestamp type, you can convert input text that contains time zone information to timestamp with the AT TIME ZONE operator:

SELECT '2019-01-01 03:00:00+02' AT TIME ZONE 'UTC';

      timezone
---------------------
 2019-01-01 01:00:00

This operator also works the other way, converting a timestamp value in a "known" timezone into timestamptz:

SELECT '2019-01-01 01:00:00'::timestamp AT TIME ZONE 'UTC';
        timezone
------------------------
 2019-01-01 02:00:00+01

This way node-postgres only have to encode and decode timestamptz values, and that works by default, without depending on specific timezone configuration of the host machine.

Generally, you are better off storing UTC timestamps in the timestamptz type. The timestamp type is for cases when you do not (yet) know the UTC value, eg. future events in a calendar.

[charmander]

@AlecZadikian9001 You’re right that the handling of timestamp in pg is strange, but to second @boromisp, you have the way timestamp and timestamptz should be used backwards. timestamptz is for instants in time, and timestamp is for when you want to start worrying about timezones. (It’s a bit of a confusing naming.)

[AlecZadikian9001]

I see, it makes sense now. So neither timestamp-ish type is storing a timezone. Using timestamptz, it's behaving more like how I'd expect, despite Postgres still showing me timezones due to my machine's locale. Still a bit unsettling, but that has nothing to do with this library.