TIMESTAMP AT 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 For details, see |
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:
For details, see Specifying Time Zones below. Vertica treats literals |
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:
If the input string includes a time zone, Vertica implicitly casts it to a For example, the following statement specifies a
=> 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 For example, the following statement specifies a => 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 => 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
orPST
- 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)