6

I'm using Node.js, Postgres, and the node-postgres library. When I try to insert a new record with the current date with code like this:

client.query('INSERT INTO ideas(date) VALUES($1)', [new Date()], ...);

And this runs fine with no errors. However, when I run a select statement against the database and log the results, the date I'm given appears as:

Wed Nov 20 2013 19:00:00 GMT-0500 (EST)

Which is fine, except that when I inserted the record, it was Thursday, November 21. And the time was 5:47, not 7:00 as the output would suggest.

I ran the code a few more times, and it stored the same inaccurate date no matter the time, even once the next hour had begun. This leads me to believe that for some reason, it's only storing the date and not the hour or minute. In addition, the fact that the date is only off by one day suggests that the problem might have something to do with the way node-postgres handles dates.

I know it's not a problem with Javascript calculating the current date when passing it into the query, because I logged new Date() and it was accurate, to the date, minute, hour, and second.

Any help on this issue would be greatly appreciated. Thanks!

maxluzuriaga
  • 1,327
  • 9
  • 16
  • 2
    Check the server `TimeZone` setting; see if Node is sending timestamp qualified times, and whether you're using `timestamp` or `timestamp with time zone` (`timestamptz`) types. Also consider just using `current_timestamp` in PostgreSQL instead of generating the time in Node. – Craig Ringer Nov 21 '13 at 23:23
  • Just tried the `current_timestamp` thing, still have the same issue. What TimeZone setting would that be? In the node server creation code? – maxluzuriaga Nov 21 '13 at 23:49
  • 1
    `SHOW TimeZone` (SQL statement) and see the PostgreSQL docs on "timezone" – Craig Ringer Nov 22 '13 at 02:15
  • Ohhh, you were right. I was using the `date` type instead of the `timestamptz`, so Postgres was only recording the date, and not the time. – maxluzuriaga Nov 22 '13 at 02:50

1 Answers1

7

The problem (thanks to Craig for pointing this out) was that I was using the date type for my Postgres column, which only records dates, and not times. Once I switched to the timestamptz type everything worked perfectly!

Community
  • 1
  • 1
maxluzuriaga
  • 1,327
  • 9
  • 16