By Oleksii Rudenko June 1, 2016 8:55 PM

Working with PostgreSQL's timestamp without timezone in Node

PostgreSQL offers a type called timestamp without timezone for storing date-time value without the timezone information. Normally, attributes of this type hold date-time values in UTC and the application logic converts these values to the user timezone.

If you make queries to PostgreSQL from NodeJS most likely you are using node-postgres or something built on top of it like pg-promise. If you also use timestamps without timezone your code may contain bug!

The issue is quite simple though: say you save 2011-01-01 12:00:00 into your database, then you read this value using node-postgres which by default converts 2011-01-01 12:00:00 to a JavaScript date object like this new Date(2011-01-01 12:00:00). And at this point the Date function parses the string and assumes that it is a timestamp in your local timezone! So if your server runs in +2, you will get a date object that says 2011-01-01 10:00:00, i.e. two hours less. Of course, you know your server’s timezone and you can convert the date back to the correct value but normally you simply don’t expect the date to be in a timezone other than UTC.

Solution # 1

Disable automatic date parsing by node-postgress and parse dates in your application:

// 1114 is OID for timestamp in Postgres
// return string as is
pg.types.setTypeParser(1114, str => str);

Solution # 2

Force conversion to UTC using, for example, momentjs:

// 1114 is OID for timestamp in Postgres
// return string as is
pg.types.setTypeParser(1114, str => moment.utc(str).format());

If you use pg-promise, you can get the access to the node-postgres instance like this:

var pgp = require('pg-promise')(/*options*/);
var types = pgp.pg.types;
types.setTypeParser(...);

If you are using a more high level library or ORM, you may want to check whether it handles the timezones properly.

Thanks for reading!