0

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

Maxim Samburskiy
  • 1,811
  • 2
  • 11
  • 13
  • 1
    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 Nov 03 '21 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 Nov 03 '21 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 '…'`](https://stackoverflow.com/q/10797720/1048572). 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 Nov 03 '21 at 14:39
  • @Bergi yes, it was session timezone. Saving dates as timestamp does not suffer from the same problem? – Maxim Samburskiy Nov 03 '21 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 Nov 03 '21 at 16:08
  • @Bergi no, I mean using `int4` – Maxim Samburskiy Nov 03 '21 at 16:13
  • `int4` is not a timestamp, it's an integer (and a rather small one). Use `timestamptz` for timestamps. – Bergi Nov 03 '21 at 16:25

1 Answers1

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

Jim Jones
  • 18,404
  • 3
  • 35
  • 44