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:
- TIMESTAMP WITHOUT TIME ZONE ⇾ TIMESTAMP WITH TIME ZONE (add time zone)
- 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)