PostgreSQL Set Time Zone

Introduction

If you plan to work with date and time data in PostgreSQL, you’ll want to ensure that you’ve set the correct time zone for your location. Fortunately, it’s easy to make this change using the psql command-line interface. In this article, we’ll explain how to connect to PostgreSQL and set the time zone.

Prerequisites

Before we proceed with our tutorial, there are a couple of important prerequisites that need to be in place. First, you’ll need to make sure that PostgreSQL is already installed on your system. You’ll also need to have psql, the command-line interface for PostgreSQL, installed. To check if PostgreSQL is installed, you can use the psql -V or postgres -V commands. Either of these commands will return the number of the version that you have installed.

Use psql to connect to a database

The first thing we’ll need to do is enter the psql interface. If you try to run the psql command by itself, it will attempt to connect to the interface using the default postgres admin role. If this happens, you might see an error stating: FATAL: role "postgres" does not exist; if this occurs, you’ll have to switch to the postgres user in your terminal using the command sudo -u postgres -i and create one.

Once you have a Postgres user set up with role attributes, you can connect to a specific database in psql using the ocmmand shown below:

NOTE: Be sure to replace objectrocket and some_db with the appropriate values for your username and database.

PostgreSQL timezones

Now that we’re connected to a PostgreSQL database, we can start working with time zones. Use the following command in the psql interface to have Postgres return your current time zone:

You’ll see something like the following:

Postgres time zone list

If you’re not sure of the exact name of the time zone you want to set, you can use the following command to get the complete list of all PosgreSQL time zones:

There are a few accepted formats and column names that can be used to reference time zones: the zone names (name), the abbreviated names (abbrev), and the UTC hourly offset integers (utc_offset). The pg_timezone_names table also has a is_dst column that indicates if a zone is currently in Daylight Savings Time.

Use the following SQL statement to have psql return just the name column from the time zone table:

Set the PostgreSQL time zone

Before we attempt to use the NOW() function, it’s important to confirm that the time zone is set properly:

If the string value used in the SET TIMEZONE command is invalid, Postgres will default to the current UTC (Coordinated Universal Time) time.

Set the Postgres time zone with an integer

You can also set the time zone using an integer value representing the hourly difference from UTC time. You can pass the value as a string or as an integer:

If you use an integer to set the time zone, the SHOW TIMEZONE command will return the UTC integer value instead of the string name for the zone:

Set the Postgres time zone with an abbreviated name

Although PostgreSQL offers the option of setting a time zone with an abbreviated name, GMT and UTC are the only abbreviated names you can use. The following example shows how you can reset your Postgres database to Greenwich Mean Time (GMT), which is equivalent to UTC:

Get a timestamp of the local time zone

You can use the 'localtime' string to get a ISO 8601 compliant timestamp that utilizes the set time zone:

Postgres timestamps

Postgres timestamps will display the set time zones by default, but there are ways you can modify the timestamp so that it’s not impacted by the time zone set by Postgres. You can also omit the time zone from your timestamps.

Postgres timestamp without time zone

We can use the SQL command shown below to have psql return a timestamp without the time zone at the end:

Default Postgres time zone

Keep in mind that the TIMEZONE value is a session-only variable– this means that it resets to the default value every time you exit and re-enter the psql command-line interface.

Permanently set the Postgres time zone

If you’d like to permanently change the default PostgreSQL time zone for all of your databases, you’ll need to modify the postgresql.conf file. Linux users will likely find the file in the /etc/postgresql directory. To locate this file, change into the directory for your PostgreSQL version and then into the /main/ directory.

After you’ve made the necessary changes, don’t forget to restart the PostgreSQL service:

Conclusion

Setting the correct time zone is important when you’re working with date and time values in PostgreSQL. It’s easy to connect to PostgreSQL and set the time zone with just a simple command. In this article, we showed you how to set the time zone and how to display a timestamp both with and without the current time zone. Using the examples we provided, you’ll be able to manage the time zone setting on your own installation of PostgreSQL.