Configure PostgreSQL time zone to UTC

[karlhorky]

Time zone inconsistencies between systems (eg. development environment and production environment) can be very difficult to debug.

Instead, set PostgreSQL to use UTC time (which seems to be best practice for deployment infrastructure, from a quick search)

TODO:

• [x] @Eprince-hub: Check into Windows
  • [x] Do we need the command? Is there a timezone = ... line in the postgresql.conf file by default with the Chocolatey install?
  • [x] What does SHOW TIMEZONE; show?
  • [x] What is the system time zone?
• [x] @Eprince-hub: Check into macOS
  • [x] Do we need the command? Is there a timezone = ... line in the postgresql.conf file by default with the Homebrew install?
  • [x] What does SHOW TIMEZONE; show?
  • [x] What is the system time zone?
• [x] @Eprince-hub: Check into Linux
  • [x] Do we need the command? Is there a timezone = ... line in the postgresql.conf fileby default with the APT install?
  • [x] What does SHOW TIMEZONE; show?
  • [x] What is the system time zone?

Useful commands:

# psql query to show database time zone
SHOW TIMEZONE;

# Show all lines in postgresql.conf
cat $PGDATA/postgresql.conf | grep timezone

# Show local system time zone
tzutil /g # Windows (unverified)
sudo systemsetup -gettimezone # macOS
timedatectl # Linux (unverified)

PostgreSQL v14

Note: The checks above should be done anyway, because we support latest PostgreSQL (currently PostgreSQL v16)

But just for documentation / posterity:

PostgreSQL v14 on macOS

On my local system (macOS PostgreSQL v14, via Homebrew), here's the output of SHOW TIMEZONE; before this config:

=> SHOW TIMEZONE;
   TimeZone
---------------
 Europe/Vienna
(1 row)

In my /opt/homebrew/var/postgresql@14/postgresql.conf file:

$ cat $PGDATA/postgresql.conf | grep timezone
log_timezone = 'Europe/Vienna'
timezone = 'Europe/Vienna'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                                        # share/timezonesets/.

My system time zone:

$ sudo systemsetup -gettimezone
Time Zone: Europe/Vienna

PostgreSQL v14 on Linux (Debian)

With psql connecting to a remote PostgreSQL v14 on a Linux (Debian) machine

=> SHOW TIMEZONE;
 TimeZone 
----------
 UTC
(1 row)

Because PostgreSQL v14 is installed remotely on another system which I don't have access to, I cannot check the postgresql.conf file or local system time zone on that machine.

[ProchaLu]

This is my output when testing on my machine:

spring2024=> SHOW TIMEZONE;
   TimeZone
---------------
 Europe/Vienna
(1 row)

➜  ~ sudo systemsetup -gettimezone

Password:
Time Zone: Europe/Vienna

➜  ~ cat $PGDATA/postgresql.conf | grep timezone
log_timezone = 'Europe/Vienna'
timezone = 'Europe/Vienna'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                    # share/timezonesets/.

[Eprince-hub]

macOS

• sudo systemsetup -gettimezone

  Time Zone: Europe/Vienna

• SHOW TIMEZONE;

  TimeZone
  ---------------
  Europe/Vienna
  (1 row)

• grep "timezone" "$PGDATA/postgresql.conf"

  log_timezone = 'Europe/Vienna'
  timezone = 'Europe/Vienna'
  #timezone_abbreviations = 'Default' 

Windows

System timezone

PowerShell: Get-TimeZone and Hyper: powershell -command "Get-TimeZone" produced the same result

// Hyper
Id                         : W. Europe Standard Time
DisplayName                : (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
StandardName               : W. Europe Standard Time
DaylightName               : W. Europe Summer Time
BaseUtcOffset              : 01:00:00
SupportsDaylightSavingTime : True

// PowerShell
Id                         : W. Europe Standard Time                                                                    
DisplayName                : (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna                               
StandardName               : W. Europe Standard Time                                                                    
DaylightName               : W. Europe Summer Time                                                                      
BaseUtcOffset              : 01:00:00                                                                                   
SupportsDaylightSavingTime : True   

• SHOW TIMEZONE;

  TimeZone                                                                                                                           
  ---------------
  Europe/Berlin
  (1 row)

• grep "timezone" "$PGDATA/postgresql.conf"

  log_timezone = 'Europe/Berlin'
  timezone = 'Europe/Berlin'
  #timezone_abbreviations = 'Default'  

Linux

System timezone running date

Mon Jul 29 11:08:30 UTC 2024

running cat /etc/timezone

Etc/UTC

• SHOW TIMEZONE;

  postgres=# SHOW TIMEZONE;
  TimeZone
  ----------
  Etc/UTC
  (1 row)

• grep "timezone" "$PGDATA/postgresql.conf"

  log_timezone = 'Etc/UTC'
  timezone = 'Etc/UTC'
  #timezone_abbreviations = 'Default'