Inconsistent (date) values when working with Timezones

[veriKami]

Some time ago I noticed "small problems" working with data (types) from json subquery (brianc/node-postgres#1876). I know that there are many similar issues, but now I had to include different time zones and "things" looks worse...

Consider The Experiment

with (postgresql.conf) timezone = 'Europe/Warsaw'
or (sql) SET TIMEZONE = 'Europe/Warsaw'
and (node) $ env TZ='Europe/Warsaw' node server.js

and query:

SELECT
    current_setting('TIMEZONE')        AS "TZ_current",
    now()                              AS "now_______",
    now()::timestamptz                 AS "now_tstz__",
    now()::timestamp                   AS "now_ts____",
    now() AT TIME ZONE 'Europe/Warsaw' AS "now_WAW___",
    now() AT TIME ZONE 'UTC'           AS "now_UTC___",
    (SELECT row_to_json(r) FROM (
        SELECT
            now()                              AS "now_______",
            now()::timestamptz                 AS "now_tstz__",
            now()::timestamp                   AS "now_ts____",
            now() AT TIME ZONE 'Europe/Warsaw' AS "now_WAW___",
            now() AT TIME ZONE 'UTC'           AS "now_UTC___"
    ) r );

the results look like this:

console.log(process.env.TZ); //: (ok) Europe/Warsaw
console.log(new Date()); //: 2019-04-14T12:38:47.301Z (ok) UTC
console.log(new Date().getTimezoneOffset()/60): //: (ok) -2

{
  "TZ_current": "Europe/Warsaw",
  "now_______": "2019-04-14T12:38:47.301Z", //: ? UTC
  "now_tstz__": "2019-04-14T12:38:47.301Z", //: ? UTC
  "now_ts____": "2019-04-14T12:38:47.301Z", //: ERROR: now_tstz__ = now_ts____
  "now_WAW___": "2019-04-14T12:38:47.301Z", //: ERROR: now_tstz__ = now_WAW___
  "now_UTC___": "2019-04-14T10:38:47.301Z", //: ERROR: now_tstz__ <> now_UTC___ 
  "row_to_json": {
    "now_______": "2019-04-14T14:38:47.301076+02:00",
    "now_tstz__": "2019-04-14T14:38:47.301076+02:00",
    "now_ts____": "2019-04-14T14:38:47.301076",
    "now_WAW___": "2019-04-14T14:38:47.301076",
    "now_UTC___": "2019-04-14T12:38:47.301076"
  }
}

types.setTypeParser(1114, (s) => s); //: timestamp
types.setTypeParser(1184, (s) => s); //: timestamptz

{
  "TZ_current": "Europe/Warsaw",
  "now_______": "2019-04-14 14:39:35.911219+02",
  "now_tstz__": "2019-04-14 14:39:35.911219+02",
  "now_ts____": "2019-04-14 14:39:35.911219",
  "now_WAW___": "2019-04-14 14:39:35.911219",
  "now_UTC___": "2019-04-14 12:39:35.911219",
  "row_to_json": {
    "now_______": "2019-04-14T14:39:35.911219+02:00",
    "now_tstz__": "2019-04-14T14:39:35.911219+02:00",
    "now_ts____": "2019-04-14T14:39:35.911219",
    "now_WAW___": "2019-04-14T14:39:35.911219",
    "now_UTC___": "2019-04-14T12:39:35.911219"
  }
}

and for me this is a Real Problem with default (js date) type.

Maybe another argument for #50 – Do not return DATE fields as Javascript Date – ???

Originally posted by @veriKami in brianc/node-postgres#1876 (comment)

[bendrucker]

I merged a change returning dates as strings in #53, to be released soon