Why do I see the timezone offset displayed in PgAdmin, for a timestamp with timezone

by

The question:

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:

2022-03-06 16:11:33+04

Why is the +04 being displayed?

I’ve looked at the following postgres guide:

https://www.postgresql.org/docs/9.2/datatype-datetime.html#:~:text=PostgreSQL%20assumes%20your%20local%20time,being%20displayed%20to%20the%20client.

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?

The Solutions:

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.

Method 1

I’m in Dublin – currently on UTC/GMT.

I did the following:

CREATE TABLE t 
(
  x TIMESTAMPTZ NOT NULL
);

then

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)

Note the short_desc field:

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:

2022-03-08 21:15:02.899933-08

Compare this with 2022-03-09 05:15:02.899933+00 – they’re the same time – just displayed differently – due entirely to my postgresql.conf setting!

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:SSZ written).

Answer:

Yes, it is the timezone setting in postgresql.conf that determines the display of the time to you! The TIMESTAMPTZ value is immutable!

You say:

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

Leave a Comment