Node-postgres date not saving accurately


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!

Problem courtesy of: maxluzuriaga


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!

Solution courtesy of: maxluzuriaga


There is currently no discussion for this recipe.

This recipe can be found in it's original form on Stack Over Flow.