# How the timezone() Function Works in PostgreSQL

> In PostgreSQL, the timezone() function converts a timestamp to a different time zone. It retrieves a new timestamp with the same value but in a different time …

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.

---
[View this page online](https://www.commandprompt.com/education/how-the-timezone-function-works-in-postgresql/)