CODEWITHSUNDEEP
postgresqltimestamppg
Disteonne
Disteonne

Reputation: 79

Why does PG timestamp give incorrect result?

Why

Select timestamptz '2022-03-19T00:00Z' as test1

give result : 2022-03-19 03:00:00+03 ??

Upvotes: 0

Views: 1756

Answers (1)

Clej
Clej

Reputation: 476

You're indicating Postgresql a timestamptz at UTC time. There, it's returning a strictly equivalent time, except it is displayed in a different time zone (UTC-03). Why does Postgresql not return the exact same thing you indicated ?

When parsing a timestamp with time zone, Postgresql internally stores it in UTC time, using the provided TZ info (here 'Z, understood as Z00, which means UTC time) to determine the offset to apply to convert it (here, you're giving a perfectly fine UTC timestamp).

When displaying data however, Postgresql relies on the internal TimeZone info defined in the postgresql.conf file to choose the time zone to use.

In your case, the local time zone info might be set to the place you live (or where your server lies), which is in UTC-03. Just type show time zone to be sure of that.

If you want to retrieve the data at timezone UTC, you have two or three options:

  • the simplest one is to precise the timezone info you want in the select statement:
select timestamptz '2022-03-19T00:00Z' at time zone 'UTC' as test1;

which gives you a timestamp without timezone though;

  • or you can set the time zone info for your local session:
set timezone='UTC';
  • or if you want to set the time zone info to UTC permanently, the timezone field of the postgresql.conf file has to be changed. This file is for instance in /opt/homebrew/var/postgres if you're running a local instance of postgresql on Mac (like me), installed from brew. On Linux, I believe it might be in /etc/postgresql.
    You need to restart your postgresql instance after the change: brew services restart postgresql on Mac for example.

Upvotes: 3

Related Questions