PostgreSQL - Change Timezone

The default timezone of a newly created database is UTC.

 

You can set the timezone to a new value sessionly or globally:

zzh@ZZHPC:~$ docker exec -it postgres16 psql -U root zimple_bank
psql (16.1)
Type "help" for help.

zimple_bank=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2023-11-29 07:29:26.963788+00
(1 row)

zimple_bank=# SHOW timezone;
 TimeZone 
----------
 UTC
(1 row)

zimple_bank=# SET timezone TO PRC;
SET
zimple_bank=# SHOW timezone;
 TimeZone 
----------
 PRC
(1 row)

zimple_bank=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2023-11-29 15:30:32.472819+08
(1 row)

zimple_bank=# \q
zzh@ZZHPC:~$ docker exec -it postgres16 psql -U root zimple_bank
psql (16.1)
Type "help" for help.

zimple_bank=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2023-11-29 07:30:51.448542+00
(1 row)

zimple_bank=# SHOW timezone;
 TimeZone 
----------
 UTC
(1 row)

zimple_bank=# ALTER DATABASE zimple_bank SET timezone TO PRC;
ALTER DATABASE
zimple_bank=# \q
zzh@ZZHPC:~$ docker exec -it postgres16 psql -U root zimple_bank
psql (16.1)
Type "help" for help.

zimple_bank=# SHOW timezone;
 TimeZone 
----------
 PRC
(1 row)

zimple_bank=# SELECT current_timestamp;
       current_timestamp       
-------------------------------
 2023-11-29 15:31:59.819894+08
(1 row)

zimple_bank=#