Understanding AT TIME ZONE

TIMESTAMPTZ AT TIME ZONE and TIMESTAMP AT TIME ZONE return date input in another time zone. How Vertica executes AT TIME ZONE varies, depending on whether the input is a TIMESTAMPTZ or TIMESTAMP. At first glance, this might be confusing. More about that later. First, let’s review AT TIME ZONE syntax:

{ TIMESTAMPTZ | TIMESTAMP [WITH TIME ZONE] } ‘input-string’ AT TIME ZONE ‘zone’

input-string	A string that concatenates a date and a time. The input string can also specify: • Calendar era, AD (default) or BC • Time zone specified as a literal such as America/Chicago, or as a UTC offset
AT TIME ZONE zone	Specifies the time zone to use in the returned timestamp, where zone is a string literal or interval that specifies a UTC offset: • AT TIME ZONE ‘time-zone-literal’ • AT TIME ZONE INTERVAL ‘utc-offset’

TIMESTAMPTZ versus TIMESTAMP Behavior

How Vertica interprets AT TIME ZONE depends on whether the date/time input is a TIMESTAMPTZ or TIMESTAMP.

TIMESTAMPTZ Input

Vertica always changes the TIMESTAMPTZ input to the time zone of the AT TIME ZONE argument. Vertica returns that time with the UTC offset of the specified time zone. If the input string omits a time zone expression, Vertica uses the UTC offset of the time zone that is configured for your system.

For example, the following statement specifies a TIMESTAMPTZ data type. The input string omits a time zone expression, so Vertica assumes the input string is in the local time zone—in this example, America/New York. Vertica returns the time of the AT TIME ZONE argument (America/Denver), which is two hours earlier: => SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; timezone --------------------- 2001-02-16 18:38:40 (1 row)

TIMESTAMP Input with Time Zone

If a TIMESTAMP input string includes a time zone, Vertica implicitly coerces it to a TIMESTAMPTZ and displays it accordingly, as described above. For example, the input string in the next statement explicitly specifies a time zone (America/Mexico City). Vertica coerces the TIMESTAMP to a TIMESTAMPTZ and returns the time of the AT TIME ZONE argument (Asia/Tokyo) : => SELECT TIMESTAMP '2001-02-16 20:38:40 America/Mexico_City' AT TIME ZONE 'Asia/Tokyo'; timezone --------------------- 2001-02-17 11:38:40 (1 row)

TIMESTAMP input with No Time Zone

If a TIMESTAMP input string specifies no time zone, Vertica performs two actions:
1. Changes the input string to the time zone of the AT TIME ZONE argument.
2. Returns the time for the current session’s time zone.

The following example assumes the America/New York time zone. The statement specifies a TIMESTAMP with no time zone: => SELECT TIMESTAMP '2017-3-14 5:30' AT TIME ZONE 'PDT'; timezone ------------------------ 2017-03-14 08:30:00-04 (1 row) Vertica executes this statement as follows:
1. Changes the input string to PDT (Pacific Daylight Time), as specified in the AT TIME ZONE clause.
2. Returns that time in the local time zone, which is three hours later.

For More Information

See TIME AT TIME ZONE and TIMESTAMP AT TIME ZONE in the Vertica documentation.