Interval Subtype Units

The following tables lists subtype units that can be specified in an interval literal, divided into major categories: 

• Year-month subtype units
• Day-time subtype units

Processing m Input

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)

=> SET INTERVALSTYLE TO UNITS;
=> 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)