TIMESTAMP AT TIME ZONE

Converts the specified TIMESTAMP or TIMESTAMPTZ (TIMESTAMP WITH TIMEZONE) to another time zone.

Converts the specified TIMESTAMP or TIMESTAMPTZ (TIMESTAMP WITH TIMEZONE) to another time zone. Vertica executes AT TIME ZONE differently, depending on whether the date input is a TIMESTAMP or TIMESTAMPTZ. See TIMESTAMP versus TIMESTAMPTZ Behavior below.

Syntax

timestamp-clause AT TIME ZONE 'zone'

Parameters

timestamp-clause
Specifies the timestamp to convert, either TIMESTAMP or TIMESTAMPTZ.

For details, see TIMESTAMP/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 details, see Specifying Time Zones below.

TIMESTAMP versus TIMESTAMPTZ behavior

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

Date input Action
TIMESTAMP

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

  1. Converts the input string to the time zone of the AT TIME ZONE argument.

  2. Returns the time for the current session's time zone.

If the input string includes a time zone, Vertica implicitly casts it to a TIMESTAMPTZ and converts it accordingly (see TIMESTAMPTZ below).

For example, the following statement specifies a TIMESTAMP with no time zone. Vertica executes the statement as follows:

  1. Converts the input string to PDT (Pacific Daylight Time).

  2. Returns that time in the local time zone, which is three hours later:

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

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

Vertica converts the input string 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 any time zone expression, so Vertica assumes the input string to be in local time zone (America/New_York) and returns the time of the AT TIME ZONE argument:

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

=> 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, so Vertica coerces the TIMESTAMP to a TIMESTAMPTZ and returns the time of the AT TIME ZONE argument:

=> 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)

Specifying time zones

You can specify time zones in two ways:

  • A string literal such as America/Chicago or PST

  • An interval that specifies a UTC offset—for example, INTERVAL '-08:00'

It is generally good practice to specify time zones with literals that indicate a geographic location. Vertica makes the necessary seasonal adjustments, and thereby avoids inconsistent results. For example, the following two queries are issued when daylight time is in effect. Because the local UTC offset during daylight time is -04, both queries return the same results:

=> SELECT TIMESTAMPTZ '2017-03-16 09:56:13' AT TIME ZONE 'America/Denver' "Denver Time";
     Denver Time
---------------------
 2017-03-16 07:56:13
(1 row)

=> SELECT TIMESTAMPTZ '2017-03-16 09:56:13' AT TIME ZONE INTERVAL '-06:00' "Denver Time";
     Denver Time
---------------------
 2017-03-16 07:56:13
(1 row)

If you issue a use the UTC offset in a similar query when standard time is in effect, you must adjust the UTC offset accordingly—for Denver time, to -07—otherwise, Vertica returns a different (and erroneous) result:

=> SELECT TIMESTAMPTZ '2017-01-16 09:56:13' AT TIME ZONE 'America/Denver' "Denver Time";
     Denver Time
---------------------
 2017-0-16 07:56:13
(1 row)

=> SELECT TIMESTAMPTZ '2017-01-16 09:56:13' AT TIME ZONE INTERVAL '-06:00' "Denver Time";
     Denver Time
---------------------
 2017-01-16 08:56:13
(1 row)

You can show and set the session's time zone with SHOW TIMEZONE and SET TIME ZONE, respectively:

=> SHOW TIMEZONE;
   name   |     setting
----------+------------------
 timezone | America/New_York
(1 row)

=> SELECT CURRENT_TIMESTAMP(0) "Eastern Daylight Time";
 Eastern Daylight Time
------------------------
 2017-03-20 12:18:24-04
(1 row)

=> SET TIMEZONE 'America/Los_Angeles';
SET

=> SELECT CURRENT_TIMESTAMP(0) "Pacific Daylight Time";
 Pacific Daylight Time
------------------------
 2017-03-20 09:18:24-07
(1 row)

Time zone literals

To view the default list of valid literals, see the files in the following directory:

opt/vertica/share/timezonesets

For example:

$ cat Antarctica.txt
...
# src/timezone/tznames/Antarctica.txt
#

AWST    28800    # Australian Western Standard Time
                 #     (Antarctica/Casey)
                 #     (Australia/Perth)
...

NZST    43200    # New Zealand Standard Time
                 #     (Antarctica/McMurdo)
                 #     (Pacific/Auckland)
ROTT   -10800    # Rothera Time
                 #     (Antarctica/Rothera)
SYOT    10800    # Syowa Time
                 #     (Antarctica/Syowa)
VOST    21600    # Vostok time
                 #     (Antarctica/Vostok)

See also