Node-postgres: Q: passing Date() variable as parameter to client.query()

[jdabbs003]

Question: What are best practices for passing Date objects as _client.query()_ parameters for _TIMESTAMP WITH TIME ZONE_ and _TIMESTAMP WITHOUT TIME ZONE_ values?

Also, when passing a Date object as a _TIMESTAMP WITH TIME ZONE_ value, is getTimezoneOffset() used to synthesize the actual time zone passed on the wire to postgres?

[joskuijpers]

I just pass them. Simply. Just query("SELECT $1::timestamptz", [myDate]) (or timestamp when not using a time zone).

You can see the date parse implementation here: https://github.com/brianc/node-pg-types/blob/master/lib/binaryParsers.js#L132
In the code you can see that only when using timestamptz the timezone within is used. If using timestamp (without timezone), only date and time is stored and the timezone is assumed to be the local one. So when storing in postgres the timezone is lost, and when getting from postgres the local timezone is assumed.

I advise to _always_ use timestamps with time zone. It is very useful information and fixes (and prevents) problems when time zones differ, either on the client side, or between pg client and pg server.

[rightfold]

I advice _not_ to always use timestamp with time zone, but instead to learn the difference between timestamp with time zone and timestamp without time zone and to use the correct tool for the job.