Date/Time Expressions

Vertica uses an internal heuristic parser for all date/time input support. Dates and times are input as strings, and are broken up into distinct fields with a preliminary determination of what kind of information might be in the field. Each field is interpreted and either assigned a numeric value, ignored, or rejected. The parser contains internal lookup tables for all textual fields, including months, days of the week, and time zones.

Vertica parses date/time type inputs as follows:

1. Break the input string into tokens and categorize each token as a string, time, time zone, or number.
2. Numeric token contains:
   • colon (:) — Parse as a time string, include all subsequent digits and colons.
   • dash (-), slash (/), or two or more dots (.) — Parse as a date string which might have a text month.
   • Numeric only — Parse as a single field or an ISO 8601 concatenated date (19990113 for January 13, 1999) or time (141516 for 14:15:16).
3. Token starts with a plus (+) or minus (–): Parse as a time zone or a special field.
4. Token is a text string: match up with possible strings.
   • Perform a binary-search table lookup for the token as either a special string (for example, today), day (for example, Thursday), month (for example, January), or noise word (for example, at, on).
   • Set field values and bit mask for fields. For example, set year, month, day for today, and additionally hour, minute, second for now.
   • If not found, do a similar binary-search table lookup to match the token with a time zone.
   • If still not found, throw an error.
5. Token is a number or number field:
   • If eight or six digits, and if no other date fields were previously read, interpret as a "concatenated date" (19990118 or 990118). The interpretation is YYYYMMDD or YYMMDD.
   • If token is three digits and a year was already read, interpret as day of year.
   • If four or six digits and a year was already read, interpret as a time (HHMM or HHMMSS).
   • If three or more digits and no date fields were found yet, interpret as a year (this forces yy-mm-dd ordering of the remaining date fields).
   • Otherwise the date field ordering is assumed to follow the DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a month or day field is found to be out of range.
6. If BC is specified: negate the year and add one for internal storage. (In the Vertica implementation, 1 BC = year zero.)
7. If BC is not specified, and year field is two digits in length: adjust the year to four digits. If field is less than 70, add 2000, otherwise add 1900.

Month Day Year Ordering

For some formats, ordering of month, day, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields.

Special Date/Time Values

Vertica supports several special date/time values for convenience, as shown below. All of these values need to be written in single quotes when used as constants in SQL statements.

The values INFINITY and -INFINITY are specially represented inside the system and are displayed the same way. The others are simply notational shorthands that are converted to ordinary date/time values when read. (In particular, NOW and related strings are converted to a specific time value as soon as they are read.)  

epoch

DATE, TIMESTAMP

INFINITY

TIMESTAMP

-INFINITY

TIMESTAMP

NOW

DATE, TIME, TIMESTAMP

TODAY

DATE, TIMESTAMP

TOMORROW

DATE, TIMESTAMP

YESTERDAY

DATE, TIMESTAMP

ALLBALLS

TIME

The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type:

• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP
• LOCALTIME
• LOCALTIMESTAMP

The latter four accept an optional precision specification. (See Date/Time Functions.) However, these functions are SQL functions and are not recognized as data input strings.