In Postgres, a time zone represents a region of the earth with a uniform standard time. Time zones allow us to convert local time to UTC(Coordinated Universal Time) or vice versa. Time with time zone is stored in PostgreSQL as a TIMESTAMPTZ(abbreviation of TIMESTAMP with TIMEZONE) data type, which includes time and time zone offset. Offsets show the difference between local and UTC.

This blog will explain what exactly TIMEZONE is and how it works in Postgres. For this purpose, the below-listed concepts will be covered in this write-up:

• How to Get/Show Database Timezone in Postgres?
• Postgres Supported Timezones.
• How to Set Database Timezone in Postgres?
• Postgres TIMEZONE() Function.
• Date/Time Functions With Time Zone

How to Get/Show Database Timezone in Postgres?

In Postgres, the “SHOW TIMEZONE” command retrieves the timezone of the current database:

SHOW TIMEZONE;

The stated command retrieves the current timezone of the “postgres” database.

Postgres Supported Timezones

In Postgres, the “pg_timezone_names” table retrieves the list of Postgres-supported timezones:

SELECT *
FROM pg_timezone_names;

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

How to Set/Change Database Timezone in Postgres?

Use the ALTER DATABASE command with the SET TO clause to set/change the timezone of a Postgres database:

ALTER DATABASE postgres SET TIMEZONE TO 'Africa/Abidjan';

Let’s verify the time zone alteration using the following command:

SHOW TIMEZONE;

The time zone of the selected database has been modified to “Africa/Abidjan”.

Postgres TIMEZONE() Function

Postgres offers a timezone() function that accepts a zone and a timestamp as arguments and converts the timestamp to some other timestamp based on the specified/given timezone:

timezone(zone, timestamp);

Specify the “timezone” of your choice in place of the “zone” argument.

Example: How Does the TIMEZONE Function Work in Postgres?

In the following code, we will pass a TIMESTAMP with a time zone to the TIMEZONE() function:

SELECT TIMEZONE('Canada/Mountain', TIMESTAMPTZ '2020-11-11 08:30:15-08');

The input timestamp has been converted according to the specified time zone.

Date/Time Functions With Time Zone

Postgres offers several date-time functions to deal with temporal data. The below-provided functions retrieve the DateTime values along with the timezone information:

- NOW(): Retrieves the current DateTime with timezone information.
- CURRENT_TIMESTAMP: Retrieves the timestamp value with timezone information.
- TO_TIMESTAMP(): Converts a DateTime string to a timestamp. Its return type is TIMESTAMPTZ.
- CURRENT_TIME: Retrieves Current Time with Timezone information.
- CLOCK_TIMESTAMP(): Retrieves the current DateTime with timezone information at which the recent transaction begins.
- STATEMENT_TIMESTAMP(): Retrieves the current DateTime with timezone information at which the current statement executes.
- TRANSACTION_TIMESTAMP(): It works the same way as the NOW() function.
- DATE_TRUNC(): Truncates/trims unnecessary values from the DateTime and retrieves a result with specific precision. Its return type is TIMESTAMP with TIMEZONE.

Let’s comprehend how these functions work via the following examples.

Example 1: Get Today’s DateTime With Timezone

In the below code, we will show how the NOW() function work in Postgres:

SELECT NOW();

The output shows that the NOW() function retrieves the current DateTime with a timezone.

Example 2: Get Today’s Time With Timezone

In the below code, we will show how the CURRENT_TIME function works in Postgres:

SELECT CURRENT_TIME;

The output shows that the “CURRENT_TIME” function retrieves the time with the timezone.

Conclusion

A time zone in PostgreSQL is a region that follows a specific set of rules for handling time. It allows us to store and display date and time values with a specific offset from UTC (Coordinated Universal Time). It allows us to accurately convert the date and time values to other time zones across different regions. This post presented a comprehensive guide on Postgres TIME ZONE with a practical demonstration.