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.

score 25 · Answer 1 · edited May 23 '17 at 12:26

25

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

edited May 23 '17 at 12:26

Community

1
1

answered Apr 06 '14 at 10:32

BadIdeaException

2,125
15
32

1

I don't know if this is still considered working, but it changes the behavior of the parser to return a string instead of a date object (at least in the current iteration of pg-promise, which uses node-postgres under the covers). This broke all of my code which expected a date to be returned, so this solution was not helpful for me. Only thing that worked for me was modifying stringValue before passing it to the date constructor: `return new Date(stringValue + '+0000')`. Would love to see a better way to make a UTC date object from an ISO 8601 string which does not specify time zone. – stone Jul 12 '16 at 23:01
1

True, but the OP said a string was sufficient. Either way, with overriding the parser it's easy to return pretty much anything you need, including a new date object. – BadIdeaException Jul 13 '16 at 05:56
1

Works for me as well, as I use other time libraries like "moment" or "luxon" anyway after fetching. Not really a fan of those Date() when they convert everything to local time... – Pencilcheck Nov 30 '17 at 12:53
All workarounds are listed here https://60devs.com/working-with-postgresql-timestamp-without-timezone-in-node.html. I ended up using the moment() solution. – justd Mar 21 '19 at 14:04

score 13 · Answer 2 · edited May 06 '21 at 22:17

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())
    );
}

In my case, what I did was avoid using Date at all until I absolutely KNEW the timezone, instead using a simple object with this schema: {year, month, day, hour, minutes, seconds}, and then constructing ISO strings from it only once I knew a timezone I wanted to construct it with. — Dennis L, Nov 10 '20 at 20:49

score 6 · Answer 3 · answered Dec 20 '13 at 23:32

6

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.

answered Dec 20 '13 at 23:32

Isaac Dontje Lindell

3,246
6
24
35

In my case, this solution is safer anyway because of Daylight Saving Time concerns. – Cody Stott Jan 23 '17 at 20:23

score 1 · Answer 4 · answered Nov 11 '20 at 10:09

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.

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

4 Answers4