current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
add comment

2 Answers

up vote 0 down vote

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.

share|improve this answer
add comment
up vote 0 down vote

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.