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:

postgres@debian11pg:/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:

postgres@debian11pg:/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:

postgres@debian11pg:/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:

postgres@debian11pg:/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:

postgres@debian11pg:/home/postgres/ [pgdev] sudo timedatectl set-timezone 'Asia/Dubai'
postgres@debian11pg:/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
postgres@debian11pg:/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:

postgres@debian11pg:/home/postgres/ [pgdev] mkdir /var/tmp/dummy
postgres@debian11pg:/home/postgres/ [pgdev] initdb -D /var/tmp/dummy
postgres@debian11pg:/home/postgres/ [pgdev] export PGPORT=8888
postgres@debian11pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/dummy start
postgres@debian11pg:/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:

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