I’m using PgAdmin 4 v6, and for some timestamp data with data type of
timestamp with time zone, I’m seeing it in PgAdmin like this:
Why is the
+04 being displayed?
I’ve looked at the following postgres guide:
It states the following:
All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the TimeZone
configuration parameter before being displayed to the client.
The reference here states the following about TimeZone configuration in Postgres:
TimeZone (string)Sets the time zone for displaying and interpreting
time stamps. The built-in default is GMT, but that is typically
overridden in postgresql.conf; initdb will install a setting there
corresponding to its system environment.
This suggests that a timezone derived somehow from the database host system is being added to postgresql.conf.
I assume that the reason I see the offset in PgAdmin is that PgAdmin is displaying a converted version of the stored value, based on timezone info for the database host system, that’s been stored in postgresql.conf. Can anyone confirm if this is what PgAdmin does when it displays data with data type
timestamp with timezone?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
I’m in Dublin – currently on UTC/GMT.
I did the following:
CREATE TABLE t ( x TIMESTAMPTZ NOT NULL );
INSERT INTO t VALUES (NOW());
Then in pgAdmin, I
SELECT * FROM t and the value I see is
x timestamp with timezone 2022-03-09 05:15:02.899933+00
Note the timezone: 2022-03-09 05:15:02.899933
+00 – because I’m on UTC/GMT.
Fine. I check my
postgresql.conf and I have
timezone = 'Europe/Dublin'.
To double check, I look at the command line:
postgres=# SELECT * FROM pg_timezone_names WHERE name = current_setting('TIMEZONE'); name | abbrev | utc_offset | is_dst ---------------+--------+------------+-------- Europe/Dublin | GMT | 00:00:00 | t (1 row)
So, belt and braces:
postgres=# SELECT name, setting, unit, short_desc FROM pg_settings WHERE setting LIKE '%ublin%'; name | setting | unit | short_desc ----------+---------------+------+----------------------------------------------------------------- TimeZone | Europe/Dublin | | Sets the time zone for displaying and interpreting time stamps. (1 row)
Sets the time zone for displaying and interpreting time stamps.
Now, I edit my
postgresql.conf and change the timezone to
America/Los_Angeles and restart my server!
Now, if I do
SELECT * FROM t; it gives me:
Compare this with
2022-03-09 05:15:02.899933+00 – they’re the same time – just displayed differently – due entirely to my
Presumably this is so that the displayed times will be meaningful to those looking at them. People won’t think they have appointments in the middle of the night &c. It can be easy to skip the timezone part of a displayed datetime/timestamp if one isn’t used to them.
I can think of one example of where it might be a good idea to have the display always in UTC/Zulu and that is in civil aviation where all times are always given in Zulu (you’ll frequently see YYYY-MM-DD HH:MM:SS
Yes, it is the
timezone setting in
postgresql.conf that determines the display of the time to you! The
TIMESTAMPTZ value is immutable!
I assume that the reason I see the offset in PgAdmin is that PgAdmin
is displaying a converted version of the stored value, based on
timezone info for the database host system, that’s been stored in
postgresql.conf. Can anyone confirm if this is what PgAdmin does when
it displays data with data type timestamp with timezone?
When I installed Windows on this machine, I chose the timezone for the PC as
Dublin, Edinburgh, Lisbon, London and when I installed PostgreSQL, the timezone in
postgresql.conf was set to
Europe/London – I reset it to
Europe/Dublin. PostgreSQL obviously makes what it thinks is a best guess based on your system settings. However, as you can see from the above, it’s easy to change this.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0