Infrastructure at your Service

Daniel Westermann

Some basics about time zones in PostgreSQL

As soon as you have an application which works across time zones you will have to deal with that properly in the database. I’ve seen many applications that didn’t care of that at the beginning, and much work had to be done to change the implementation afterwards. The one and only recommendation is: Do it properly from the beginning and you don’t have to think much about it in the future. PostgreSQL comes with all you need.

Let’s start from the beginning: How does PostgreSQL store the local time and time zone information? If I ask for the current date and time on my installation I get this:

postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2021-09-23 08:19:58.921732+02
(1 row)

You can’t know it, but this is my current local time in Switzerland. What you see at the end of the output is the offset to UTC, in my case plus 2 hours. Internally, PostgreSQL stores everything as UTC and the output gets converted to whatever you want to see. If I want to know the current local time for Lisbon, I can do it like this:

postgres=# select current_timestamp at time zone 'Europe/Lisbon';
          timezone          
----------------------------
 2021-09-23 07:23:26.222616
(1 row)

… or I can do it like this:

postgres=# set timezone = 'Europe/Helsinki';
SET
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2021-09-23 09:25:07.545244+03
(1 row)

You can use all the time zones listed in pg_timezone_names:

postgres=# select * from pg_timezone_names limit 10;
        name         | abbrev | utc_offset | is_dst 
---------------------+--------+------------+--------
 GMT0                | GMT    | 00:00:00   | f
 HST                 | HST    | -10:00:00  | f
 MST                 | MST    | -07:00:00  | f
 WET                 | WEST   | 01:00:00   | t
 Turkey              | +03    | 03:00:00   | f
 Cuba                | CDT    | -04:00:00  | t
 CST6CDT             | CDT    | -05:00:00  | t
 Canada/Central      | CDT    | -05:00:00  | t
 Canada/Newfoundland | NDT    | -02:30:00  | t
 Canada/Eastern      | EDT    | -04:00:00  | t
(10 rows)

Knowing all this, one point should be clear already: Use timestamp with time zone if you want to be on the save side. In contrast to Oracle, the “date” data type does not contain any time information:

postgres=# select '2021-09-22 11:11:11'::date;
    date    
------------
 2021-09-22
(1 row)

The “timestamp” data type does not contain any time zone information:

postgres=# select '2021-09-22 11:11:11+02'::timestamp;
      timestamp      
---------------------
 2021-09-22 11:11:11
(1 row)

Don’t use “date” and “timestamp” if you need to work across time zones, they just are not made for that. There is a clear statement about that in the PostgreSQL Wiki. Make your life easy: Use timestamptz.

Note that there are also the “time” and “timetz” data types:

postgres=# select '11:11:11'::time;
   time   
----------
 11:11:11
(1 row)

postgres=# select '11:11:11'::timetz;
   timetz    
-------------
 11:11:11+00
(1 row)

The same applies here: Don’t use them. Maybe you have a use case for time, but timetz should not be used at all (check the Wiki entry linked above once more).

My PostgreSQL instance is apparently running with a time zone setting of ‘Europe/Zurich’ (or any other time zone which has an offset of plus 2 hours from UTC):

postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2021-09-23 09:02:02.631441+02
(1 row)

How can I change that? Quite easy:

postgres=# select name, setting  from pg_settings where setting like '%Zurich%';
     name     |    setting    
--------------+---------------
 log_timezone | Europe/Zurich
 TimeZone     | Europe/Zurich
(2 rows)

There is one parameter for the timestamps when it comes to logging, and another one which specifies the time zone the server uses as it’s default configuration:

postgres=# set TimeZone='Mexico/General';
SET
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2021-09-23 02:05:27.149078-05
(1 row)

The remaining question is: How is the default server setting selected? It did not explicitly set the “TimeZone” parameter but still I got the correct value for my current time zone. This is were the operating system comes into the game. My local time zone settings on the OS are:

[email protected]:/home/postgres/ [pgdev] timedatectl 
               Local time: Thu 2021-09-23 08:55:31 CEST
           Universal time: Thu 2021-09-23 06:55:31 UTC
                 RTC time: Thu 2021-09-23 06:55:32
                Time zone: Europe/Zurich (CEST, +0200)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no

The principle for the operating system (Debian 11 in my case) is more or less the same as with PostgreSQL. You can set, whatever you have available:

[email protected]:/home/postgres/ [pgdev] timedatectl list-timezones | head -5
Africa/Abidjan
Africa/Accra
Africa/Algiers
Africa/Bissau
Africa/Cairo

The setting which is currently in use is defined by a symlink:

[email protected]:/home/postgres/ [pgdev] ls -la /etc/localtime 
lrwxrwxrwx 1 root root 33 Jul  9 08:40 /etc/localtime -> /usr/share/zoneinfo/Europe/Zurich

You can either change the symlink manually to whatever you see in that directory:

[email protected]:/home/postgres/ [pgdev] ls -l /usr/share/zoneinfo/ | head -5
total 328
drwxr-xr-x  2 root root   4096 Jul  9 08:40 Africa
drwxr-xr-x  6 root root  20480 Jul  9 08:40 America
drwxr-xr-x  2 root root   4096 Jul  9 08:40 Antarctica
drwxr-xr-x  2 root root   4096 Jul  9 08:40 Arctic

.. or you can (and should) use timedatectl for that:

[email protected]:/home/postgres/ [pgdev] sudo timedatectl set-timezone 'Asia/Dubai'
[email protected]:/home/postgres/ [pgdev] timedatectl
               Local time: Thu 2021-09-23 11:22:36 +04
           Universal time: Thu 2021-09-23 07:22:36 UTC
                 RTC time: Thu 2021-09-23 07:22:37
                Time zone: Asia/Dubai (+04, +0400)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no
[email protected]:/home/postgres/ [pgdev] date
Thu 23 Sep 2021 11:22:38 AM +04

Now we changed the configuration of the operating system. Let’s initialize a fresh PostgreSQL cluster and check the default once more:

[email protected]:/home/postgres/ [pgdev] mkdir /var/tmp/dummy
[email protected]:/home/postgres/ [pgdev] initdb -D /var/tmp/dummy
[email protected]:/home/postgres/ [pgdev] export PGPORT=8888
[email protected]:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/dummy start
[email protected]:/home/postgres/ [pgdev] psql -p 8888 -c "select current_timestamp"
       current_timestamp       
-------------------------------
 2021-09-23 11:24:01.759933+04
(1 row)

Here we go: The default you get, depends on the configuration of your operating system. You can still change that if you are not happy with the default:

[email protected]11pg:/home/postgres/ [pgdev] psql -p 8888 -c "alter system set TimeZone='Europe/Zurich'"
[email protected]:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/dummy restart
[email protected]:/home/postgres/ [pgdev] psql -p 8888 -c "select current_timestamp"
       current_timestamp       
-------------------------------
 2021-09-23 09:28:56.233463+02
(1 row)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure