Why postgres uses different timezone for formatting?

Question

I'm running following query from my JS code using pg:

SELECT begin_at, to_char(begin_at, 'DD.MM') AS dt
FROM tasks
WHERE begin_at >= $1
ORDER BY begin_at

Column begin_at has type timestamptz.

On my PC I get dates in my timezone (UTC+4) { begin_at: "2021-11-02T19:00:00.000Z", dt: "03.11" } and on server I get { begin_at: "2021-11-02T19:00:00.000Z", dt: "02.11" } for the same database.

Both running node v16.8.0, postgres 13, both have same time and set postgres timezone to Asia/Yekaterinburg

can you try this SET TIMEZONE = 'Asia/Yekaterinburg'; SELECT to_char('2021-11-02T19:00:00.000Z'::timestamp with time zone, 'DD.MM') AS dt? — Jim Jones
– Jim Jones, Commented Nov 3, 2021 at 13:53
Try to_char(begin_at AT TIME ZONE 'Asia/Yekaterinburg', 'DD.MM') AS dt. Also I'd be curious what the result of to_char(begin_at, 'TZ') AS tz is in your two systems. — Bergi
– Bergi, Commented Nov 3, 2021 at 14:21
Wait, forget about AT TIME ZONE. Apparently the only way to influence rendering timezone in to_char is to set local timezone to '…'. I really would suggest to just query the timestamp from postgres, and do the date formatting in the last possible moment; in your case using a timezone-aware date library (e.g. Temporal or Luxon) in node.js. — Bergi
– Bergi, Commented Nov 3, 2021 at 14:39
@Bergi yes, it was session timezone. Saving dates as timestamp does not suffer from the same problem? — Maxim Samburskiy
– Maxim Samburskiy, Commented Nov 3, 2021 at 16:04
You mean saving dates in a column of type timestamp (without timezone)? That actually suffers from worse problems, as now the values change (and not just the formatting) when you have a different session timezone. — Bergi
– Bergi, Commented Nov 3, 2021 at 16:08

Jim Jones · Accepted Answer · 2021-11-04 08:04:56Z

0

The column begin_at is most likely declared without the time zone or the session has a different time zone. See the difference yourself:

SET TIMEZONE = 'Asia/Yekaterinburg';
SELECT to_char('2021-11-02T19:00:00.000Z'::timestamp with time zone, 'DD.MM');

 to_char 
---------
 03.11

without time zone:

SET TIMEZONE = 'Asia/Yekaterinburg';
SELECT to_char('2021-11-02T19:00:00.000Z'::timestamp, 'DD.MM');

 to_char 
---------
 02.11

To be sure of the session's timezone you might wanna try SHOW TIMEZONE:

SHOW TIMEZONE;

 TimeZone 
----------
 Etc/UTC

Check this demo to see the effect of SET TIMEZONE in the seesion: db<>fiddle

edited Nov 4, 2021 at 8:04

answered Nov 3, 2021 at 14:00

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bergi Over a year ago

OP wrote "Column begin_at has type timestamptz".

Jim Jones Over a year ago

@Bergi fair enough, in this case I would guess the issue is in the sessions' timezone, but it is hard to be sure. Thanks for pointing out. Cheers

Bergi Over a year ago

Yeah, from all I read about to_char the issue must be the session timezone

Collectives™ on Stack Overflow

Why postgres uses different timezone for formatting?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related