Date/Time Data Types

Vertica supports the full set of SQL date and time data types. In most cases, a combination of DATE, DATETIME, SMALLDATETIME, TIME, TIMESTAMP WITHOUT TIME ZONE, and TIMESTAMP WITH TIME ZONE, and INTERVAL provides a complete range of date/time functionality required by any application.

In compliance with the SQL standard, Vertica also supports the TIME WITH TIME ZONE data type.

The following table lists the characteristics about the date/time data types. All these data types have a size of 8 bytes.

Name
Description
Low Value
High Value
Resolution
DATE
Dates only
 (no time of day)
~ 25e+15 BC
~ 25e+15 AD
1 day
TIME [(p)] 
Time of day only
 (no date)
00:00:00.00
23:59:60.999999
1 μs
TIMETZ [(p)] 
Time of day only,
 with time zone
00:00:00.00+14
23:59:59.999999-14
1 μs
TIMESTAMP [(p)]
Both date and time,
 without time zone
290279-12-22 19:59:05.224194 BC
294277-01-09 04:00:54.775806 AD
1 μs
TIMESTAMPTZ [(p)] 
Both date and time,
 with time zone
290279-12-22 19:59:05.224194 BC UTC
294277-01-09 04:00:54.775806 AD UTC
1 μs
INTERVAL [(p)]DAY TO SECOND
Time intervals
-106751991 days  04:00:54.775807
+-106751991 days  04:00:54.775807 
1 μs
INTERVAL [(p)]YEAR TO MONTH
Time intervals
~ -768e15 yrs
~ 768e15 yrs
1 month

Time Zone Abbreviations for Input

Vertica recognizes the files in /opt/vertica/share/timezonesets as date/time input values and defines the default list of strings accepted in the AT TIME ZONE zone parameter. The names are not necessarily used for date/time output—output is driven by the official time zone abbreviations associated with the currently selected time zone parameter setting.

Notes

• In Vertica, TIME ZONE is a synonym for TIMEZONE.
• Vertica uses Julian dates for all date/time calculations, which can correctly predict and calculate any date more recent than 4713 BC to far into the future, based on the assumption that the average length of the year is 365.2425 days.
• All date/time types are stored in eight bytes.
• A date/time value of NULL appears first (smallest) in ascending order.

• All the date/time data types accept the special literal value NOW to specify the current date and time. For example:

  => SELECT TIMESTAMP 'NOW';
             ?column?
  ----------------------------
   2012-03-13 11:42:22.766989
  (1 row)
  

• In Vertica, the INTERVAL data type is SQL:2008 compliant and allows modifiers, called interval qualifiers, that divide the INTERVAL type into two primary subtypes, DAY TO SECOND (the default) and YEAR TO MONTH. You use the SET INTERVALSTYLE command to change the intervalstyle run-time parameter for the current session.

  Intervals are represented internally as some number of microseconds and printed as up to 60 seconds, 60 minutes, 24 hours, 30 days, 12 months, and as many years as necessary. Fields can be positive or negative.