Pg-promise: Timezone applied to TIMESTAMP WITHOUT TIME ZONE when it shouldn't

[daveisfera]

Timezone offset is being applied to columns of type TIMESTAMP WITHOUT TIME ZONE when it shouldn't be. I tested this with 5.6.8 and 6.5.1 and both showed the issue.

Here's example code to demonstrate the issue:

#!/usr/bin/env node

const pgp = require('pg-promise')();
const os = require('os');

const host = process.argv[2] || 'localhost';
const database = process.argv[3] || os.userInfo().username;
const user = process.argv[4] || os.userInfo().username;

const config = { host, database, user };
const db = pgp(config);
db.tx(t => {
    return t.batch([
        t.none('DROP TABLE IF EXISTS test;'),
        t.none('CREATE TABLE test (a TIMESTAMP WITHOUT TIME ZONE);'),
        t.none('INSERT INTO test (a) VALUES (\'2017-01-02T03:04:05.678\');'),
    ])
    .then(() => t.any('SELECT a FROM test;'))
    .then(rows => {
        console.log(rows);
    });
})
.catch(err => {
    console.log('Error:', err);
});

[vitaly-t]

This is how the driver works presently. You can try logging it against the driver or on StackOverflow.

There have been many issues logged against timestamp in the past, and more than one solution proposed.

I won't be going into more details, since it is outside of the scope for this library. See the driver.

[vitaly-t]

Note that if you are formatting it on your side, and simply passing in a string, then it is the server that applies the timezone, not the driver.

[daveisfera]

The server isn't applying any timezone offset. If you do the select from a Postgres client or another language, then you get the correct/expected answer.

As you mentioned, there are multiple issues, so how do I go about disabling the application of the local timezone offset as described in the upstream issues?

[vitaly-t]

You will get a better answer on StackOverflow for this. Issues related to Date/Time conversion are outside of this library, which doesn't convert them in any way. This library uses exactly what the driver provides.

[daveisfera]

I understand that, but node-postgres exposes a way to disable this conversion, so how do I access that through pg-promise?

[vitaly-t]

Do you mean how to access the driver? See How to access the instance of node-postgres that's used?

[daveisfera]

Yes, that's what I was looking for.

For the sake of anyone that comes across this issue. Here's how I fixed it:

const pgp = require('pg-promise')();
const moment = require('moment');
// 1114 is OID for timestamp in Postgres
pgp.pg.types.setTypeParser(1114, str => moment.utc(str).format());

[vitaly-t]

It is important to know that this behavior changed in v5.4.1.