In PostgreSQL, changing the time zone of a database is a straightforward yet essential task. Incorrect time stamps can cause various ambiguities/issues, from misconceptions and miscommunications to inappropriate data analysis and reporting. However, by updating the database's time zone, you can ensure that all clients/team members are on the same page, regardless of their location.

This blog will show you how to change the timezone of a Postgres database via practical demonstration. So, let’s get started.

How to Find All TIMEZONE in Postgres?

Execute the “SELECT *” command for the “pg_timezone_names” table to see the list of all timezones supported by Postgres:

SELECT * FROM pg_timezone_names;

The “pg_timezone_names” table contains all necessary details regarding Postgres-supported timezones.

How to Change/Alter the Timezone of a Database in PostgreSQL?

PostgreSQL provides an “ALTER DATABASE” command that can be used with the “SET TIMEZONE” clause to change the timezone of a Postgres database. To do that, the ALTER DATABASE command must be executed as follows:

ALTER DATABASE db_name
SET TIMEZONE TO 'new_timezone';

Specify the database name in place of “db_name”.

Example: Changing the Timezone of a Specific Database

To find the current timezone in PostgreSQL, you need to execute the following command:

SHOW TIMEZONE;

The output indicates that the current timezone is “America/Los_angeles”. Let’s find the current timestamp by executing the below-provided command:

SELECT CURRENT_TIMESTAMP;

Suppose we want to change the current time zone to "Australia/Brisbane". For this purpose, we will use the ALTER DATABASE command as follows:

ALTER DATABASE example
SET TIMEZONE TO 'Australia/Brisbane';

Let’s check the modified timezone of the “example” database via the below-mentioned command:

SHOW TIMEZONE;

The output snippet shows that the TIMEZONE has been changed/modified successfully. Now to find the current timestamp, you need to use the following statement:

SELECT CURRENT_TIMESTAMP;

This is how you can alter the timezone of a database in Postgres.

Note: When you run a database on a server, the server's time zone might not match your team members' time zone. This can cause confusion and errors, especially while working with timestamped data. By changing the time zone of your database, you can remove such kind of issues/ambiguities.

Conclusion

PostgreSQL provides an “ALTER DATABASE” command that can be used with the “SET TIMEZONE” clause to change the timezone of a Postgres database. The timezone must be valid/Postgres supported. Execute the “SELECT *” command for the “pg_timezone_names” table to see the list of all time zones supported by Postgres. This blog explained how to change the timezone of a Postgres database via practical demonstration.