This is the multipage printable view of this section.
Click here to print.
Return to the regular view of this page.
Interval literal
A literal that represents a time span.
A literal that represents a time span.
Syntax
[ @ ] [] { quantity subtypeunit }[...] [ AGO ]
Parameters
@
 Ignored
 (minus)
 Specifies a negative interval value.
quantity
 Integer numeric constant
 [subtypeunit](/en/sqlreference/languageelements/literals/datetimeliterals/intervalliteral/intervalsubtypeunits/)
 See Interval subtype units for valid values. Subtype units must be specified for yearmonth intervals; they are optional for daytime intervals.
AGO
 Specifies a negative interval value.
AGO
and  (minus) are synonymous.
Notes

The amounts of different units are implicitly added up with appropriate sign accounting.

The boundaries of an interval constant are:

The range of an interval constant is
+/– 2^{63} – 1
microseconds.

In Vertica, interval fields are additive and accept large floatingpoint numbers.
Examples
See Specifying interval input.
1  Interval subtype units
The following tables lists subtype units that you can specify in an interval literal, divided into major categories:.
The following tables lists subtype units that you can specify in an interval literal, divided into major categories:
Yearmonth 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 kiloyear: 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 , wk , week , wks , weeks 

Daytime 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 

Vertica uses context to interpret the input unit m
as months or minutes. For example, the following command creates a onecolumn 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:
unit [ TO unit ] [ (p) ]
where:
If an interval omits an interval qualifier, the default is DAY TO SECOND(6)
.
Interval qualifiers are divided into two categories:
Daytime interval qualifiers
Qualifier 
Description 
DAY 
Unconstrained 
DAY TO HOUR 
Span of days and hours 
DAY TO MINUTE 
Span of days and minutes 
DAY TO SECOND [( p )] 
Span of days, hours, minutes, seconds, and fractions of a second. 
HOUR 
Hours within days 
HOUR TO MINUTE 
Span of hours and minutes 
HOUR TO SECOND [( p )] 
Span of hours and seconds 
MINUTE 
Minutes within hours 
MINUTE TO SECOND [( p )] 
Span of minutes and seconds 
SECOND [( p )] 
Seconds within minutes 
Yearmonth interval qualifiers
YEAR
 Unconstrained
MONTH
 Months within year
YEAR TO MONTH
 Span of years and months
Note
Vertica also supports INTERVALYM
, which is an alias for INTERVAL YEAR TO MONTH
. Thus, the following two statements are equivalent:
=> SELECT INTERVALYM '1 2';
?column?

12
(1 row)
=> SELECT INTERVAL '1 2' YEAR TO MONTH;
?column?

12
(1 row)
Examples
See Controlling interval format.