PostgreSQL AT TIME ZONE Operator

Summary: in this tutorial, you will learn how to use the PostgreSQL AT TIME ZONE operator to convert a timestamp or a timestamp with time zone to a different time zone.

Introduction to the PostgreSQL AT TIME ZONE operator

In PostgreSQL, the AT TIME ZONE is an operator that allows you to convert a timestamp or a timestamp with time zone to a different time zone.

The AT TIME ZONE operator can be useful when you want to perform timezone conversions within your SQL queries.

Here’s the syntax of the AT TIME ZONE operator:

timestamp_expression AT TIME ZONE target_timezoneCode language: SQL (Structured Query Language) (sql)

In this syntax:

• timestamp_expression is a timestamp or timestamp with time zone value that you want to convert.
• target_timezone is the target time zone to which you want to convert. This can be either a time zone name or an expression that evaluates to a time zone name.

The AT TIME ZONE operator always returns a value of type TIMESTAMP WITH TIME ZONE.

PostgreSQL AT TIME ZONE operator examples

Let’s explore some examples of using the AT TIME ZONE operator. It is assumed that the server’s time zone is 'America/Los_Angeles'.

If you want to have consistent results like the following examples, you can set the PostgreSQL server’s timezone to 'America/Los_Angeles' by executing the following statement in any PostgreSQL client (such as pgAdmin or psql):

set timezone to 'America/Los_Angeles'Code language: JavaScript (javascript)

Once you execute the command, you can verify it by showing the current timezone:

show timezone;Code language: SQL (Structured Query Language) (sql)

Output:

      TimeZone
---------------------
 America/Los_Angeles
(1 row)

1) Basic AT TIME ZONE operator example

The following example uses the AT TIME ZONE operator to convert a timestamp to Coordinated Universal time (UTC):

SELECT TIMESTAMP '2024-03-21 10:00:00' AT TIME ZONE 'UTC';Code language: JavaScript (javascript)

Output:

        timezone
------------------------
 2024-03-21 03:00:00-07
(1 row)Code language: CSS (css)

2) Converting timestamp with time zone

The following example uses the AT TIME ZONE operator to convert a timestamp with time zone to UTC:

SELECT TIMESTAMP WITH TIME ZONE '2024-03-21 10:00:00-04' AT TIME ZONE 'UTC';Code language: SQL (Structured Query Language) (sql)

Output:

      timezone
---------------------
 2024-03-21 14:00:00
(1 row)Code language: CSS (css)

3) Using the AT TIME ZONE operator with time zone abbreviation

The following query uses the AT TIME ZONE operator to convert a timestamp to Pacific Standard Time (PST) from the default time zone:

SELECT TIMESTAMP '2024-03-21 10:00:00' AT TIME ZONE 'PST';Code language: SQL (Structured Query Language) (sql)

Output:

        timezone
------------------------
 2024-03-21 11:00:00-07
(1 row)Code language: CSS (css)

4) Converting a timestamp using a time zone offset

The following example uses the AT TIME ZONE operator to convert a timestamp using a time zone offset:

SELECT TIMESTAMP '2024-03-21 10:00:00' AT TIME ZONE '-08:00';Code language: SQL (Structured Query Language) (sql)

Output:

        timezone
------------------------
 2024-03-20 19:00:00-07
(1 row)Code language: CSS (css)

5) Converting a timestamp using named time zones

The following example uses the AT TIME ZONE operator to convert a timestamp using a named time zone:

SELECT TIMESTAMP '2024-03-21 10:00:00' AT TIME ZONE 'America/New_York';Code language: SQL (Structured Query Language) (sql)

Output:

        timezone
------------------------
 2024-03-21 07:00:00-07
(1 row)Code language: CSS (css)

Please note that PostgreSQL uses the IANA time zone database for time zone information.

Summary

• Use the AT TIME ZONE operator to convert a timestamp to a different time zone.