While retrieving Timestamp values from DB it is adding 4 hours to it.

[SagarPattnayak]

Hi,
I am using "pg" in one of my nodeapp. The timestamp values i insert programmatically is inserting correct time. However while retrieving the same via pool.query, it is adding 4 hours to it. How to address this?

File1 is from DB & File2 id while retrieving.

Appreciate any feedback suggestion.

[charmander]

See #2390 (comment). If your column represents moments in time, though – it sounds like it does – it should probably be a timestamp with time zone. That data type works intuitively in pg.

[vitaly-t]

timestamp with time zone = timestamptz 😉

[SagarPattnayak]

I have tried both with timestamp and timestamptz datatype. Same issue. So as a work around I am using varchar as datatype for datetime fields.

[charmander]

If the same thing is happening with timestamptz, there’s probably something incorrect about one of the queries involved, the way you’re processing the results, or the way you’re passing in the parameter. Without seeing any code it’s hard to say though.

[msantana-dev]

See my comment here #2530 (comment)

[jpschw]

For anyone else having trouble - You're probably testing locally and it's picking up your local timezone from NodeJS.
Put this at the start of your program if you intend to always work in the same timezone across all envs. In this case UTC.
process.env.TZ = "UTC+0";

[mfvas]

Another way to "fix" this is to set:

pg.defaults.parseInputDatesAsUTC = true;

which makes it assume that all your DB timestamps are in UTC time, as far as I understand.