pg is ignoring local timezone and returning UTC strings offset by +3 hours

[msantana-dev]

It ignores

• The OS timezone
• process.env.TZ
• timestamp with timezone column

Time in database: 2021-04-26 16:37:31
Time in pg query result: 2021-04-26 19:37:31

I'm doing no processing to result.rows. Also no SQL processing, just a plain SELECT.

pg: 8.5.1
Node: 14.15.5
OS: Kubuntu 18.04

[charmander]

Please show how you’re logging the data in each case. The results of a simplified query, like

const result = await pool.query('SELECT now()');
console.log(result.rows[0], new Date());

could help too!

[msantana-dev]

@charmander

At first I thought some pg bug was messing up the timestamps, but now I know this isn't the case.

Shortly after reporting this issue I found out that the string representation of Date objects is always in UTC format,
like 2021-04-27T08:36:16.397Z.

pg maps timetamptz columns to Date objects, so you'll get UTC when converting those Date objects into string.

Casting timestamptz to varchar will bypass the conversion and output a more intuitive string like
2021-04-27 05:36:16.397-03, which is [local time]-[time zone]

Maybe this behavior could be documented, but I think my problem is solved.

Thank your for caring.