Time Zone Values

Vertica attempts to be compatible with the SQL standard definitions for time zones. However, the SQL standard has an odd mix of date and time types and capabilities. Two obvious problems are:

• Although the DATE type does not have an associated time zone, the TIME type can. Time zones in the real world have little meaning unless associated with a date as well as a time, since the offset may vary through the year with daylight-saving time boundaries.
• Vertica assumes your local time zone for any data type containing only date or time.
• The default time zone is specified as a constant numeric offset from UTC. It is therefore not possible to adapt to daylight-saving time when doing date/time arithmetic across DST boundaries.

  UTC is an abbreviation of Coordinated Universal Time (in English), the high-precision atomic time standard that replaced Greenwich Mean Time on 1 January 1972 as the basis for the main reference time scale or civil time in various regions. UTC is also referred to by the military and civil aviation as Zulu time (Z).

To address these difficulties, Vertica recommends using date/time types that contain both date and time when using time zones. We recommend not using the type TIME WITH TIME ZONE (though it is supported by Vertica for legacy applications and for compliance with the SQL standard).

Time zones, and time-zone conventions, are influenced by political decisions, not just earth geometry. Time zones around the world became somewhat standardized during the 1900's, but continue to be prone to arbitrary changes, particularly with respect to daylight-savings rules.

Daylight saving time starts on March 11 at 2:00 am in 2007.

Vertica currently supports daylight-savings rules over the time period 1902 through 2038 (corresponding to the full range of conventional Unix system time). Times outside that range are taken to be in "standard time" for the selected time zone, no matter what part of the year they fall in.