PostgreSQL Set Time Zone
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.
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:
psql -U objectrocket -d some_db
NOTE: Be sure to replace
some_db with the appropriate values for your username and database.
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:
some_db=# SHOW TIMEZONE;
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:
SELECT * FROM pg_timezone_names;
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 (
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:
SELECT name FROM pg_timezone_names;
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:
some_db=# SHOW TIMEZONE;
Set the Postgres time zone with an abbreviated name
Although PostgreSQL offers the option of setting a time zone with an abbreviated name,
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:
SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
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
After you’ve made the necessary changes, don’t forget to restart the PostgreSQL service:
sudo systemctl restart postgresql.service
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started