Use node-postgres to get Postgres "timestamp without timezone" in utc

Question

I've got some timestamps stored as the Postgres type timestamp without time zone.

I'll use the timestamp 2013-12-20 20:45:27 as an example. I'm intending that this represent a UTC timestamp.

In psql, if I run the query SELECT start_time FROM table_name WHERE id = 1, I get back that timestamp string, as expected: 2013-12-20 20:45:27.

However, if in my Node application, I use the node-postgres library to run that same query, I get back a timestamp in the local timezone: Fri Dec 20 2013 20:45:27 GMT-0600 (CST). This is a Javascript date object, but it's already stored as that timezone. What I really want is a date object (or even just a string) that represents 2013-12-20 20:45:27 GMT+0000. I already know that this time is UTC.

I've tried setting the timezone param in my postgresql.conf file to: timezone = 'UTC', with no difference in results.

What am I doing wrong?

EDIT

The issue seems to be in this file: https://github.com/brianc/node-postgres/blob/master/lib/types/textParsers.js

If the date string returned from Postgres doesn't have a time-zone specified (i.e. Z, or +06:30, then it just constructs a JavaScript date object, which I believe will just include the local time zone. I either need to change my app to store time zones in the DB or override this converter.

Answer 1

It's not the best solution, but I just switched to using the Postgres type timestamp with time zone and made sure that all dates I persisted to the DB were in UTC.

Answer 2

Not to revive an old question, but seeing how I just had the exact same issue here, there is an alternative solution that works by overriding the type parser to use for timestamp without time zone:

var pg = require('pg');
var types = pg.types;
types.setTypeParser(1114, function(stringValue) {
return stringValue;
});

This will keep node-pg from parsing the value into a Date object and give you the raw timestamp string instead.

Source: Got it from node-postgres issues

Answer 3

You can modify the parser, as @BadIdeaException suggests. Following are more details on why it doesn't work as expected, and two possible solutions.

For columns with type timestamp without time zone, the parser receives a string in ISO 8601 format, with no time zone specified: 2016-07-12 22:47:34

Any time you create a Date object in Javascript, if you do not specify a time zone, it assumes the date is in the current time zone. For UTC dates, which by definition are in the GMT time zone, this will give you a date with the incorrect absolute value (date.value), unless your Javascript happens to be running in the GMT time zone.

Therefore, that ISO 8601 string can't be directly converted into a UTC date by the Date constructor. Your options are: Modify the string so that it will be interpreted as UTC:

var pg = require('pg');
var types = pg.types;
types.setTypeParser(1114, function(stringValue) {
    return new Date(stringValue + "+0000");
});

or let your date be created with the "wrong" (current) time zone, and then extract the values for it (still in your current time zone), and then use those values to generate a date in the UTC time zone. Note that Date.UTC() returns a date value rather than an object, which can then be passed to the Date constructor.

types.setTypeParser(1114, function(stringValue) {

    var temp = new Date(stringValue);
    return new Date(Date.UTC(
        temp.getFullYear(), temp.getMonth(), temp.getDate(), temp.getHours(), temp.getMinutes(), temp.getSeconds(), temp.getMilliseconds())
    );
}

Answer 4

I was wondering where did @BadIdeaException got the number 1114 from. In typescript, can see the values from interface.

import { types } from 'pg';
types.setTypeParser(types.TypeId.TIME, (timeStr) => timeStr);
types.setTypeParser(types.TypeId.TIMESTAMP, (timeStr) => timeStr);
types.setTypeParser(types.TypeId.TIMESTAMPTZ, (timeStr) => timeStr);

This will override all timestamp fields parser and prevent the fields from getting wrongly parsed to Date object.