This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

SET TIME ZONE

Changes the TIME ZONE run-time parameter for the current.

Changes the TIME ZONE run-time parameter for the current session. Use SHOW TIMEZONE to show the session's current time zone.

If you set the timezone using POSIX format, the timezone abbreviation you use overrides the default timezone abbreviation. If the date style is set to POSTGRES, the timezone abbreviation you use is also used when converting a timestamp to a string.

Syntax

SET TIME ZONE [TO] { value | 'value' }

Parameters

value
One of the following:

  • A time zone literal supported by Vertica. To view the default list of valid literals, see the files in the following directory:

    /opt/vertica/share/timezonesets

  • A signed integer representing an offset from UTC in hours

  • A time zone literal with a signed integer offset. For example:

    => SET TIME ZONE TO 'America/New York -3'; -- equivalent to Pacific time

  • An interval value

  • Constants LOCAL and DEFAULT, which respectively set the time zone to the one specified in environment variable TZ, or if TZ is undefined, to the operating system time zone.

Only valid (timezone+offset) combination are acceptable as parameter for this function.

Privileges

None

Examples

=> SET TIME ZONE TO DEFAULT;
=> SET TIME ZONE TO 'PST8PDT'; -- Berkeley, California
=> SET TIME ZONE TO 'Europe/Rome'; -- Italy
=> SET TIME ZONE TO '-7'; -- UDT offset equivalent to PDT
=> SET TIME ZONE TO INTERVAL '-08:00 HOURS';

See also

Using time zones with Vertica

1 - Time zone names for setting TIME ZONE

The time zone names listed below are recognized by Vertica as valid settings for the SQL time zone (the TIME ZONE run-time parameter).

The time zone names listed below are recognized by Vertica as valid settings for the SQL time zone (the TIME ZONE run-time parameter).

These names are not the same as the names shown in /opt/vertica/share/timezonesets, which are recognized by Vertica in date/time input values. The TIME ZONE names listed below imply a local Daylight Saving Time rule, where date/time input names represent a fixed offset from UTC.

In many cases, the same zone has several names. These are grouped together. The list is sorted primarily by commonly used zone names.

In addition to the names in the list, Vertica accepts time zone names as one of the following:

  • STDoffset

  • STDoffsetDST

where STD is a zone abbreviation, offset is a numeric offset in hours west from UTC, and DST is an optional Daylight Saving Time zone abbreviation, assumed to stand for one hour ahead of the given offset.

For example, if EST5EDT were not already a recognized zone name, Vertica accepts it as functionally equivalent to USA East Coast time. When a Daylight Saving Time zone name is present, Vertica assumes it uses USA time zone rules, so this feature is of limited use outside North America.

Time zone

  • Africa
  • America
  • Antarctica
  • Asia
  • Atlantic
  • Australia

  • CET

  • EET

  • Etc/GMT

    • GMT

    • GMT+0

    • GMT-0

    • GMT0

    • Greenwich

    • Etc/Greenwich

  • Europe

  • Factory

  • Indian

  • MET

  • Pacific

  • UCT Etc

  • UCT

  • UTC

    • Universal Zulu

    • Etc/UTC

    • Etc/Universal

    • Etc/Zulu

  • WET