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

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