Library is converting my datetime string to UTC

[andrewackerman]

I'm working on a DB that was converted from MySQL to Postgres using a migration script. While converting the database interactions from mysql to node-postgres, I noticed that my test SQL queries were not being stored correctly - all my datetimes that were being input as ISO-8601 formatted strings with an explicit UTC flag ("YYYY-MM-DDThh:mm:ss.sssZ") were getting treated as though they were local timestamps and being implicitly converted to the "correct" UTC timezone.

There doesn't appear to be any official way to override this behavior, and as such it is seriously getting in the way of my conversion. I tried changing the datetime strings to signify UTC using the timezone-offset format ("YYYY-MM-DDThh:mm:ss.sss+00:00"), but it made no difference.

[charmander]

all my datetimes that were being input as ISO-8601 formatted strings with an explicit UTC flag ("YYYY-MM-DDThh:mm:ss.sssZ") were getting treated as though they were local timestamps and being implicitly converted to the "correct" UTC timezone

Can you provide an example query and show what was stored in the database?

[andrewackerman]

I can't show specific examples as I have since moved to a different library and modified my table. The essence is as I described, though. If I executed an INSERT or an UPDATE that included an ISO-8601 date string with a UTC marker (such as the zulu form "2018-10-22T00:00:00Z" or the time-offset form "2018-10-22T00:00:00+00:00"), it would show up in the database as "2018-10-22 06:00:00+00". This is consistent with the timestamp getting interpreted as a local timestamp (-6 hours from UTC). Since the timestamp was marked as UTC in the string, this is erroneous behavior.

[charmander]

If it was actually a string, that’s probably not related to pg; pg doesn’t modify your query or string parameters. (It does pick a format for JavaScript Date object parameters.) Maybe you went through timestamp (which would drop any timezone indicator) followed by timestamp with time zone (which would default to your PostgreSQL timezone)?

=> SET TIME ZONE -6;
SET

=> SELECT '2018-10-22T00:00:00Z'::timestamp::timestamptz AT TIME ZONE 'UTC';
      timezone       
---------------------
 2018-10-22 06:00:00
(1 row)

[andrewackerman]

My database is hosted on an AWS RDS instance, and the timestamp column was set to use UTC as the timezone. I had already thought this might be the issue, as the table originally came from a migration from a MySQL database and had a datetime type instead of a timestamptz type, but after correcting the column type, the issue remained.

The string originated from an API call, and I passed the string as-is without conversion or modification to the query. A query would've looked something like this:

INSERT INTO table_name (name_col, timestamp_col) 
VALUES ('some name', '2018-10-22T00:00:00Z');

After which the timestamp in the database would reflect a UTC timestamp of '2018-10-22T06:00:00+00'. This would be correct for a datetime string from a local timezone, but not from a UTC datetime string.

After switching to a different library, I would run the same query without any non-library-specific modifications to my code and it would execute correctly. I'm fully aware that the fault could very well be coming from somewhere in my code, but if the same query worked out-of-the-box with a different library, then I'm less inclined to believe that to be the case.

[charmander]

Which library did you switch to? Maybe it sets the timezone to UTC as a connection default. Also:

After which the timestamp in the database would reflect a UTC timestamp of '2018-10-22T06:00:00+00'.

How did you check this?

[andrewackerman]

I switched to pg-promise. And perhaps it does set the connection timezone to UTC by default, but as I said in the other issue thread, doing that for this library is a non-obvious and not-quite-trivial task. This library should really have an option to pass in a config object to be able to set those options without having to dig through and modify the library's source files. I mean, what's the point of having "defaults" if there's no way to override them?

But I digress. The point of this issue thread is that conversions shouldn't be happening at all, regardless of the connection timezone. If the passed string is clearly marked as a timestamp with integrated timezone information (in this case, UTC) using the ISO-8601 standard, it shouldn't matter what the connection timezone is because the timezone information for the timestamp is included in the string itself. I don't understand why this is even an issue, as I'm fairly confident that PostgreSQL is fully capable of parsing and handling ISO-8601 timestamp strings correctly on its own.

To answer your other question, I checked the value of the created entry using both TablePlus and pgAdmin 4 (I haven't yet decided which I'm going to stick with long-term). I used these same clients to verify that pg-promise was creating the rows correctly.

[charmander]

doing that for this library is a non-obvious and not-quite-trivial task

which is too bad, but it would also be an incorrect fix. I can’t imagine pg-promise doing anything to affect the behaviour here other than changing some connection-level setting, so it sounds like the problem isn’t fixed.

Please try reproducing the problem with a minimal schema, and post the full table definition and script.

[stasandk]

'use strict'

const { Pool, types } = require('pg')
const moment = require('moment')

// Force conversion to UTC using moment
types.setTypeParser(1114, str => moment.utc(str).format())

// Disable automatic date parsing by node-postgres and parse dates in your application
// types.setTypeParser(1114, str => str)

const pool = new Pool()

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err)
  process.exit(-1)
})

module.exports = pool

[guikubivan]

I took a much simpler approach since I'm working only with nodejs and expect my timestamps to be strings in UTC:

const { Pool, types } = require('pg')

// Override timestamp conversion to force timestamp to be inserted in UTC
types.setTypeParser(1114, (str) => {
  const utcStr = `${str}Z`;
  return new Date(utcStr).toISOString();
});
...

[tim-phillips]

For future visitors (it took me a minute to figure out this issue only applies to the timestamp type)

Type 1114 is timestamp. If you store your dates in postgresql in UTC as timestamptz, then you don't need to coerce into UTC with types.setTypeParser, the built-in parser should work fine. To keep dates in UTC on the backend, I always do a toISOString() on my dates before I send them to the api/database.

[noinkling]

To summarize and hopefully clear up any confusion:

When you supply a JS Date object as an input value, the library converts it to a string literal that Postgres can understand. By default, this literal will be in local time, with the appropriate timezone offset, rather than UTC.

This is fine for timestamp with time zone a.k.a. timestamptz because you end up with the same thing stored either way (barring a rare edge case that has to do with the precision of JS's .getTimezoneOffset()), and parsing back to a Date object doesn't require any assumptions.

But the timestamp and date types just ignore the offset part on input, there's no automatic conversion to UTC first, so the value ends up being in local time. Since this is usually not what you want, you can fix it by doing:

pg.defaults.parseInputDatesAsUTC = true;

// In pg-promise:
pgp.pg.defaults.parseInputDatesAsUTC = true;

// Note that supplying it as part of the connection options won't work,
// even though it probably should

...or by calling .toISOString() like @tim-phillips says (just beware that this method may have issues for dates far in the past/future since the format isn't 100% compatible with Postgres).

The caveat with this is that the output parsing done by postgres-date also assumes local time for timestamp and date types, so those output parsers need to be overridden (as others have shown) if you value your sanity and want roundtrips to succeed.

As for the default behaviour being changed, #783 seems to indicate that this is unlikely (it may not be worth the breaking change in any case).