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
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,
// 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!