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:
1 | psql -U objectrocket -d some_db |
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:
1 | SHOW TIMEZONE; |
You’ll see something like the following:
1 2 3 4 5 | some_db=# SHOW TIMEZONE; TimeZone ----------------- America/Chicago (1 ROW) |
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:
1 | 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 (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:
1 | 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:
1 | SET TIMEZONE='Asia/Manila'; |
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:
1 | SET timezone=-4; |
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:
1 2 3 4 5 | some_db=# SHOW TIMEZONE; TimeZone ---------- <-04>+04 (1 ROW) |
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:
1 | SET TIMEZONE='GMT'; |
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:
1 | SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime'; |
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:
1 | SELECT NOW()::TIMESTAMP; |
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:
1 | sudo systemctl restart 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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started