Node's Postgres module pg returns wrong date

Question

I have declared a date column in Postgres as date.

When I write the value with node's pg module, the Postgres Tool pgAdmin displays it correctly.

When I read the value back using pg, Instead of plain date, a date-time string comes with wrong day.

e.g.:

Date inserted:              1975-05-11
Date displayed by pgAdmin:  1975-05-11
Date returned by node's pg: 1975-05-10T23:00:00.000Z

Can I prevent node's pg to appy time-zone to date-only data? It is intended for day of birth and ihmo time-zone has no relevance here.

This looks like a bug in node-pg. You're using Brianc's driver ? — Denys Séguret, Jul 24 '15 at 10:42
It scares me. I've just proposed a customer to switch from Qt to Node+Web. And the application is full of date/time data — Valentin H, Jul 24 '15 at 10:44
@DenysSéguret: yes. http://github.com/brianc/node-postgres, "name": "pg", "version": "4.4.0" — Valentin H, Jul 24 '15 at 10:47
This doesn't look right: https://github.com/brianc/node-pg-types/blob/master/lib%2FbinaryParsers.js#L132 (and I checked the type for date is 1082) — Denys Séguret, Jul 24 '15 at 10:52
It might be a good idea to create a issue in the GitHub project. A solution might be to register a more relevant parser for the date type. — Denys Séguret, Jul 24 '15 at 10:57
OK, I did it: https://github.com/brianc/node-postgres/issues/818 — Valentin H, Jul 24 '15 at 11:37
I have here Postgres 9.4, table with column of type `date`, using `pg` v.4.4, and I'm not seeing the issue described. Is the problem specific to time going over 23:00? — vitaly-t, Jul 25 '15 at 12:43
On which time-zone are you? If the offset is 0 (GMT) you won't notice the problem, I guess. — Valentin H, Jul 25 '15 at 13:41
I did this, works great: https://github.com/brianc/node-postgres/issues/429; the code are `pg.types.setTypeParser(1114, function(stringValue) { console.log(stringValue); return new Date(Date.parse(stringValue + "+0000")); });` — Yang, Jan 07 '16 at 10:41

Valentin H · Accepted Answer · 2015-07-24T16:32:38.877

EDIT Issue response from Developer on github

The node-postgres team decided long ago to convert dates and datetimes without timezones to local time when pulling them out. This is consistent with some documentation we've dug up in the past. If you root around through old issues here you'll find the discussions.

The good news is its trivially easy to over-ride this behavior and return dates however you see fit.

There's documentation on how to do this here: https://github.com/brianc/node-pg-types

There's probably even a module somewhere that will convert dates from postgres into whatever timezone you want (utc I'm guessing). And if there's not...that's a good opportunity to write one & share with everyone!

Original message Looks like this is an issue in pg-module. I'm a beginner in JS and node, so this is only my interpretation.

When dates (without time-part) are parsed, local time is assumed. pg\node_modules\pg-types\lib\textParsers.js

if(!match) {
    dateMatcher = /^(\d{1,})-(\d{2})-(\d{2})$/;
    match = dateMatcher.test(isoDate);
    if(!match) {
      return null;
    } else {
      //it is a date in YYYY-MM-DD format
      //add time portion to force js to parse as local time
      return new Date(isoDate + ' 00:00:00');

But when the JS date object is converted back to a string getTimezoneOffset is applied. pg\lib\utils.js s. function dateToString(date)

this answer is awesome. totally made doing what i needed to do easier! — gonzofish, Apr 22 '17 at 13:49
@Valentin, would you care to share how exactly it is "trivially easy" to get node-pg to return objects without taking TZ into account? A code sample would be great — tonysepia, Jul 28 '19 at 18:40

score 3 · Answer 2 · answered May 25 '22 at 11:25

3

Just override node-postgres parser for the type date (1082) and return the value without parsing it:

import pg from pg

pg.types.setTypeParser(1082, value => value)

answered May 25 '22 at 11:25

Shiva127

2,413
1
23
27

score 1 · Answer 3 · answered Apr 12 '20 at 16:34

Another option is change the data type of the column:

You can do this by running the command:

ALTER TABLE table_name
ALTER COLUMN column_name_1 [SET DATA] TYPE new_data_type,
ALTER COLUMN column_name_2 [SET DATA] TYPE new_data_type,
...;

as descripted here.

I'd the same issue, I changed to text.

Node's Postgres module pg returns wrong date

3 Answers3