# PostgreSQL TIMEZONE | Explained With Examples

> 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 or vice versa.

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-timezone-explained-with-examples/)