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

user_1576908 0
Viewed 28086

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.

node-postgres node.js postgresql
4 Answers

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.

user_66990690
Related