Date or time literal input must be enclosed in single quotes. Input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others.
Vertica handles date/time input more flexibly than the SQL standard requires. The exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones are described in Date/time expressions.
1 - Time zone values
Vertica attempts to be compatible with the SQL standard definitions for time zones.
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. 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 can 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.
To address these difficulties, OpenText recommends using Date/Time types that contain both date and time when you use time zones. OpenText recommends that you do not use the type TIME WITH TIME ZONE, even though it is supported it 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.
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 in which they occur.
Example
Description
PST
Pacific Standard Time
-8:00
ISO-8601 offset for PST
-800
ISO-8601 offset for PST
-8
ISO-8601 offset for PST
zulu
Military abbreviation for UTC
z
Short form of zulu
2 - Day of the week names
The following tokens are recognized as names of days of the week:.
The following tokens are recognized as names of days of the week:
Day
Abbreviations
SUNDAY
SUN
MONDAY
MON
TUESDAY
TUE, TUES
WEDNESDAY
WED, WEDS
THURSDAY
THU, THUR, THURS
FRIDAY
FRI
SATURDAY
SAT
3 - Month names
The following tokens are recognized as names of months:.
The following tokens are recognized as names of months:
Vertica uses context to interpret the input unit m as months or minutes. For example, the following command creates a one-column table with an interval value:
=> CREATE TABLE int_test(i INTERVAL YEAR TO MONTH);
Given the following INSERT statement, Vertica interprets the interval literal 1y 6m as 1 year 6 months:
=> INSERT INTO int_test VALUES('1y 6m');
OUTPUT
--------
1
(1 row)
=> COMMIT;
COMMIT
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT * FROM int_test;
i
-----------------
1 year 6 months
(1 row)
The following ALTER TABLE statement adds a DAY TO MINUTE interval column to table int_test:
=> ALTER TABLE int_test ADD COLUMN x INTERVAL DAY TO MINUTE;
ALTER TABLE
The next INSERT statement sets the first and second columns to 3y 20m and 1y 6m, respectively. In this case, Vertica interprets the m input literals in two ways:
For column i, Vertica interprets the m input as months, and displays 4 years 8 months.
For column x, Vertica interprets the m input as minutes. Because the interval is defined as DAY TO MINUTE, it converts the inserted input value 1y 6m to 365 days 6 minutes:
=> INSERT INTO int_test VALUES ('3y 20m', '1y 6m');
OUTPUT
--------
1
(1 row)
=> SELECT * FROM int_test;
i | x
------------------+-----------------
1 year 6 months |
4 years 8 months | 365 days 6 mins
(2 rows)
4.2 - Interval qualifier
Specifies how to interpret and format an interval literal for output, and optionally sets precision.
Specifies how to interpret and format an interval literal for output, and optionally sets precision. Interval qualifiers are composed of one or two units:
p specifies precision, an integer between 0 and 6.
Note
Precision only applies to SECOND units, specifying the number of decimal digits to show after the seconds value decimal point. The default precision for SECOND is 6.
When SECOND is the second unit of a qualifier—for example, DAY TO SECOND or MINUTE TO SECOND—it has a precision of 2 places before the decimal point.
For example:
=> SELECT INTERVAL '6 122.538987' MINUTE TO SECOND (5);
?column?
-------------
08:02.53899
(1 row)