PG Promise not returning "timestamp with time zone" fields correctly

Question

I'm using pg-promise lib to work with Postgres DB. I don't understand why querying directly DB with

SELECT date FROM ro WHERE id = 13;

returns

date          
------------------------
 2017-01-19 00:00:00+02
(1 row)

and this pgp call:

var sql = 'SELECT date from ro WHERE id = 1366';
    Dbh.odb.any(sql)
      .then(ro => {
        console.log(ro);
        res.ok(ro)
      })

returns

{
    "date": "2017-01-18T22:00:00.000Z"
}

What I expect is

{
   "date": "2017-01-19T00:00:00.000Z"
}

Answer 1

Are you running psql on the same machine that you're running pg-promise?

From the docs on timestamp with timezone

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

So timezones are stored in UTC, and retrieved based on the TimeZone parameter, from that docs (slightly cleaned up)

The TimeZone configuration parameter can be set,

1. in the file postgresql.conf
2. In any of the other standard ways described in Chapter 19.
3. The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.
4. The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

So you could always,

var sql = `SELECT date AT TIME ZONE '+02' from ro WHERE id = 1366`;
    Dbh.odb.any(sql)
      .then(ro => {
        console.log(ro);
        res.ok(ro)
      })

Or set the timezone for the client, session, or server.

You can find the potential values for timezone here

SELECT * FROM pg_timezone_names();