SELECT ' 07:09:19- 10' AT TIME ZONE 'America/Chicago' Īgain notice the missing time zone designations in the results. SELECT ' 07:09:19' AT TIME ZONE 'America/Chicago' 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: This is useful because normally you would need to change your TimeZone setting to see values in other time zones.
REQUEST A SOFTWARE DEMO WITH TIMEZONE FULL
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). The time zone is not being added to the date and time. SELECT ' 07:09:19- 06':: timestamptz AT TIME ZONE 'America/Chicago' SELECT ' 07:09:19- 05':: timestamptz AT TIME ZONE 'America/Chicago' SELECT ' 07:09:19- 04':: timestamptz AT TIME ZONE 'America/Chicago' In these cases, because the inputs are TIMESTAMP WITH TIME ZONE, time zone designations in the strings are significant: SELECT ' 07:09:19-04'::timestamptz AT TIME ZONE ' Asia/Tokyo' SELECT ' 07:09:19-04'::timestamptz AT TIME ZONE ' America/Los_Angeles' SELECT ' 07:09:19-04'::timestamptz AT TIME ZONE ' America/Chicago' 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 ' 07:09:19- 12':: timestamp AT TIME ZONE 'America/Chicago' SELECT ' 07:09:19- 10':: timestamp AT TIME ZONE 'America/Chicago' SELECT ' 07:09:19':: timestamp AT TIME ZONE 'America/Chicago' This behavior is also shown in AT TIME ZONE: This is because casting a value to TIMESTAMP WITHOUT TIME ZONE ignores any specified time zone: It doesn't matter if a time zone designation is specified in the ::timestamp string - only the date and time are used. 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). SELECT ' 07:09:19':: timestamp AT TIME ZONE ' Asia/Tokyo' SELECT ' 07:09:19':: timestamp AT TIME ZONE ' America/Los_Angeles' SELECT ' 07:09:19':: timestamp AT TIME ZONE ' America/Chicago' First, let's see #1, AT TIME ZONE adding time zone designations: It is kind of odd for AT TIME ZONE to be used for both purposes, but the SQL standard requires this. TIMESTAMP WITH TIME ZONE ⇾ TIMESTAMP WITHOUT TIME ZONE (shift time zone).TIMESTAMP WITHOUT TIME ZONE ⇾ TIMESTAMP WITH TIME ZONE (add time zone).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 ( ::timestamp tz) to be shifted to non-local time zones and the time zone designation removed. I then updated the Postgres documentation to explain it better, and here is what I found.įirst, AT TIME ZONE has two capabilities. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it out.
I saw AT TIME ZONE used in a query, and found it confusing.