PostgreSQL provides a built-in function named timezone() that converts a timestamp to a different time zone. The timezone() function does not change the value of the input timestamp; instead, it simply retrieves a new timestamp with the same value but in a different time zone.

This blog post will explain the working of the timezone() function with practical examples.

How Does the TIMEZONE() Function Work in Postgres?

In Postgres, the timezone() function accepts two values as arguments: a zone and a timestamp. As a result, it converts a timestamp to some other timestamp based on the zone specified within the timezone() function:

timezone(tz, timestamp);

Here, tz represents a timezone based on which the given timestamp will be converted.

Let’s put it into practice for a profound understanding!

How to Check the Current TIMEZONE in Postgres?

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”.

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.

Example 1: How to Convert a TIMESTAMP(with timezone) to Another TIMESTAMP in Postgres?

Let’s pass a TIMESTAMP with timezone to the TIMEZONE() function and see how it works:

SELECT TIMEZONE('Australia/Melbourne', TIMESTAMP with TIME ZONE '2022-12-12 10:00:00-01');

The output shows that if the given timestamp contains a timezone, then the timezone() function shifts the original timestamp value to the specified time zone and retrieves the value without a time zone.

For a profound understanding, let’s change the timezone value in the specified timestamp:

SELECT TIMEZONE('Australia/Melbourne', TIMESTAMP with TIME ZONE '2022-12-12 10:00:00+01');

In the above query, we changed the timezone value from “-01” to “+01”; consequently, you will get the following output:

The output snippet proves that the original timestamp value has been modified according to the specified timezone.

Suppose a user specifies a “TIMESTAMP with TIME ZONE” in the TIMEZONE() function. However, he didn’t specify the time zone in the original timestamp; then, the original timestamp value will be shifted to the local time zone:

SELECT TIMEZONE('Australia/Melbourne', TIMESTAMP with TIME ZONE '2022-12-12 10:00:00');

The output shows that the original timestamp value has been shifted to the local timezone.

Example 2: How to Convert a TIMESTAMP(without timezone) to Another TIMESTAMP in Postgres?

Suppose you want to convert a timestamp that doesn't contain a timezone. In that case, the result will be retrieved based on the timezone settings, and the timezone will be shifted/added with the original timestamp:

SELECT TIMEZONE('Australia/Melbourne', TIMESTAMP without TIME ZONE '2022-12-12 10:00:00');

This is how you can convert a TIMESTAMP(without a timezone) to another zone via the TIMEZONE() function in Postgres.

Example 3: How to Convert a CURRENT_TIMESTAMP to Another TIMESTAMP in Postgres?

This example explains converting the current timestamp via the TIMEZONE() function. In this example, we will convert the current TIMESTAMP(with time zone) to "Australia/Melbourne" timezone using the TIMEZONE() function:

SELECT CURRENT_TIMESTAMP, TIMEZONE('Australia/Melbourne', CURRENT_TIMESTAMP);

Since the CURRENT_TIMESTAMP function contains a timezone, so the TIMEZONE() function will retrieve a timestamp without a time zone:

The output shows that the TIMEZONE() function shifts the original timestamp to the specified timezone.

Conclusion

In PostgreSQL, the timezone() function converts a timestamp to a different time zone. The timezone() function does not change the value of the input timestamp; instead, it simply retrieves a new timestamp with the same value but in a different time zone. It accepts a Postgres-supported timezone and converts the given timestamp based on the specified timezone. This post explained the basic syntax, usage, and practical examples of the Postgres’ timezone() function.