Enter the terms you wish to search for.

Postgres AT TIME ZONE Explained

January 24, 2023

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it out. I then updated the Postgres documentation to explain it better, and here is what I found.

First, AT TIME ZONE has two capabilities. It allows time zones to be added to date/time values that lack them (TIMESTAMP WITHOUT  TIME ZONE, ::timestamp), and allows TIMESTAMP WITH  TIME ZONE values (::timestamptz) to be shifted to non-local time zones and the time zone designation removed.

In summary, it allows:

  1. TIMESTAMP WITHOUT  TIME ZONE ⇾ TIMESTAMP WITH  TIME ZONE (add time zone)
  2. TIMESTAMP WITH  TIME ZONE ⇾ TIMESTAMP WITHOUT  TIME ZONE (shift time zone)

It is kind of odd for AT TIME ZONE to be used for both purposes, but the SQL standard requires this.

First, let's see #1, AT TIME ZONE adding time zone designations:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Chicago';

        timezone

------------------------

 2018-09-02 08:09:19-04


SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Los_Angeles';

        timezone

------------------------

 2018-09-02 10:09:19-04


SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'Asia/Tokyo';

        timezone

------------------------

 2018-09-01 18:09:19-04

 

What is basically happening above is that the date and time are interpreted as being in the specified time zone (e.g., America/Chicago), a TIMESTAMP WITH  TIME ZONE value is created, and the value displayed in the default time zone (-04). It doesn't matter if a time zone designation is specified in the ::timestamp string — only the date and time are used.

This is because casting a value to TIMESTAMP WITHOUT  TIME ZONE ignores any specified time zone:

SELECT '2018-09-02 07:09:19'::timestamp;

        timezone

------------------------

 2018-09-02 07:09:19-04


SELECT '2018-09-02 07:09:19-10'::timestamp;

        timezone

------------------------

 2018-09-02 07:09:19-04


SELECT '2018-09-02 07:09:19-12'::timestamp;

        timezone

------------------------

 2018-09-02 07:09:19-04

 

This behavior is also shown in AT TIME ZONE:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Chicago';

        timezone

------------------------

 2018-09-02 08:09:19-04


SELECT '2018-09-02 07:09:19-10'::timestamp AT TIME ZONE 'America/Chicago';

        timezone

------------------------

 2018-09-02 08:09:19-04


SELECT '2018-09-02 07:09:19-12'::timestamp AT TIME ZONE 'America/Chicago';

        timezone

------------------------

 2018-09-02 08:09:19-04

 

The second use of AT TIME ZONE (#2) is for removing time zone designations by shifting the TIMESTAMP WITH  TIME ZONE value to a different time zone and removing the time zone designation:

SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 06:09:19


SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Los_Angeles';

      timezone

---------------------

 2018-09-02 04:09:19


SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'Asia/Tokyo';

      timezone

---------------------

 2018-09-02 20:09:19

 

In these cases, because the inputs are TIMESTAMP WITH  TIME ZONE, time zone designations in the strings are significant:

SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 06:09:19


SELECT '2018-09-02 07:09:19-05'::timestamptz AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 07:09:19


SELECT '2018-09-02 07:09:19-06'::timestamptz AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 08:09:19

 

The time zone is not being added to the date and time. Rather, the full date/time/time zone value is shifted to the desired time zone (America/Chicago), and the time zone designation removed (TIMESTAMP WITHOUT  TIME ZONE). This is useful because normally you would need to change your TimeZone setting to see values in other time zones.

Without the cast, AT TIME ZONE inputs are assumed to be TIMESTAMP WITH  TIME ZONE, and the local time zone is assumed if not specified:

SELECT '2018-09-02 07:09:19' AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 06:09:19


SELECT '2018-09-02 07:09:19-10' AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 12:09:19

 

Again notice the missing time zone designations in the results.

The most interesting queries are these two, though they return the same output as input:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-02 07:09:19


SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';

        timezone

------------------------

 2018-09-02 07:09:19-04

 

As you can see the two AT TIME ZONE calls cancel each other out. The first creates a TIMESTAMP WITH  TIME ZONE in the America/Chicago time zone using the supplied date and time, and then shifts the value to that same time zone, removing the time zone designation. The second creates a TIMESTAMP WITHOUT  TIME ZONE value in the same time zone, then creates a TIMESTAMP WITH  TIME ZONE value using the date and time in the default time zone (TimeZone).

Using different time zones for the two calls yields useful results:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';

      timezone

---------------------

 2018-09-01 17:09:19

 

This gives the America/Chicago time for the supplied Asia/Tokyo time — quite useful.

I have updated the Postgres documentation to be clearer about AT TIME ZONE. Hopefully, that change and this blog post make the feature less confusing, or more so. 

 

(Article originally published in Bruce's personal blog - Monday, February 11, 2019 @ https://momjian.us/main/blogs/pgblog/2019.html#February_11_2019)

 

​

Popular Links
Featured Links
Resources
Share this

Relevant Blogs

More Blogs

An Overview of PostgreSQL Indexes

h2 { text-align: left !important; } .summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023