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:
- Break the input string into tokens and categorize each token as a string, time, time zone, or number.
- 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).
- Token starts with a plus (+) or minus (–): Parse as a time zone or a special field.
- 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.
- 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
orYYMMDD
. - 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
orHHMMSS
). - 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.
- 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
- If BC is specified: negate the year and add one for internal storage. (In the Vertica implementation, 1 BC = year zero.)
- 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.
Gregorian years AD 1–99 can be entered as 4 digits with leading zeros— for example, 0099 = AD 99.
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.)
String | Valid Data Types | Description |
---|---|---|
|
|
1970-01-01 00:00:00+00 (UNIX SYSTEM TIME ZERO) |
|
|
Later than all other time stamps |
|
|
Earlier than all other time stamps |
|
|
Current transaction's start time Note: |
|
|
Midnight today |
|
|
Midnight tomorrow |
|
|
Midnight yesterday |
|
|
00:00:00.00 UTC |
The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type:
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.