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

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


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


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


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.