Understanding AT TIME ZONE

[Deleted User] · March 2017

At Vertica, we get lots of questions about AT TIME ZONE. Using a TIMESTAMP data type with AT TIME ZONE works differently than using AT TIME ZONE with a TIMESTAMPTZ data type. That's probably why it's confusing.

So let's try to explain how it works so it's easy to understand.

How to Use AT TIME ZONE

TIMESTAMP AT TIME ZONE converts the specified TIMESTAMP or TIMESTAMPTZ (TIMESTAMP WITH TIMEZONE) to another time zone.

Vertica's execution of AT TIME ZONE depends on whether the date input is a TIMESTAMP or TIMESTAMPTZ. More about that later. First, the basics.

Syntax

timestamp_clause AT TIME ZONE 'zone'

Parameters

timestamp_clause: Specifies the timestamp to convert, either TIMESTAMP or TIMESTAMPTZ.

AT TIME ZONE zone: Specifies the time zone to use in the timestamp conversion, where zone is a literal or interval that specifies a UTC offset:
• AT TIME ZONE INTERVAL 'utc-offset'
• AT TIME ZONE 'time-zone-literal'

For a list of valid time zones, go to /opt/vertica/share/timezones.

For a list of valid time zone sets, go to /opt/vertica/share/timezonesets.

TIMESTAMP Versus TIMESTAMPTZ Behavior

How Vertica interprets AT TIME ZONE depends on whether the date input is a TIMESTAMP or TIMESTAMPTZ. The easiest way to explain this is with examples.

TIMESTAMP Input with No Time Zone

If a TIMESTAMP input string specifies no time zone, Vertica performs two actions:

Converts the input string to the time zone of the AT TIME ZONE argument.
If the AT TIME ZONE clause is not used, returns the time for the current session's time zone.

TIMESTAMP Input with Time Zone

If a TIMESTAMP input string includes a time zone, Vertica implicitly casts it to a TIMESTAMPTZ and converts it accordingly.

For example, suppose you are in the America/New York time zone. The following 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. Converts 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:

=> SELECT TIMESTAMP '2017-3-14 5:30' AT TIME ZONE 'PDT';
        timezone
------------------------
 2017-03-14 08:30:00-04
(1 row)

TIMESTAMPTZ Input with Time Zone

Vertica always converts TIMESTAMPTZ input to the time zone of the AT TIME ZONE argument and returns that time.

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)

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)

For More Information

See TIMESTAMP/TIMESTAMPTZ.