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)
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)