Interval Subtype Units

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

Year-Month Subtype Units

Subtypes Units Notes
Millennium mil, millennium, millennia, mils, millenniums  
Century c, cent, century, centuries  
Decade dec, decs, decade, decades  
Year a Julian year: 365.25 days
  ka Julian kilo-year: 365250 days
  y, yr, yrs, year, years Calendar year: 365 days
Quarter q, qtr, qtrs, quarter, quarters  
Month m, mon, mons, months, month Vertica can interpret m as minute or month, depending on context. See Processing m Input below.
Week w, week, weeks  

Day-Time Subtype Units

Subtypes Units Notes
Day d, day, days
Hour h, hr, hrs, hour, hours  

Minute

 m, min, mins, minute, minutes

Vertica can interpret input unit m as minute or month, depending on context. See Processing m Input below.
Second s, sec, secs, second, seconds
Millisecond ms, msec, msecs, msecond, mseconds, millisecond, milliseconds  
Microsecond us, usec, usecs, usecond, useconds, microseconds, microsecond  

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)
=> COMMIT;
COMMIT
=> SET INTERVALSTYLE TO UNITS;
SET
=> SELECT * FROM int_test;
        i
-----------------
 1 year 6 months
(1 row)

The SET INTERVALSTYLE statement changes interval output to include subtype unit identifiers. For details, see Setting Interval Unit Display.

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)