Date/time data types

Vertica supports the full set of SQL date and time data types.

Vertica supports the full set of SQL date and time data types.

The following rules apply to all date/time data types:

  • All have a size of 8 bytes.

  • A date/time value of NULL is smallest relative to all other date/time values,.

  • 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 the date/time data types accept the special literal value NOW to specify the current date and time. For example:

    => SELECT TIMESTAMP 'NOW';
         ?column?
---------------------------
 2020-09-23 08:23:50.42325
(1 row)

  • By default, Vertica rounds with a maximum precision of six decimal places. You can substitute an integer between 0 and 6 for p to specify your preferred level of precision.

The following table lists specific attributes of date/time data types:

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 DAY TO SECOND [(p)] Time intervals -106751991 days 04:00:54.775807 +-106751991 days 04:00:54.775807 1 μs
INTERVAL 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.

In this section