This is the multi-page 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 subtype-unit }[...] [ AGO ]

Parameters

@
Ignored
- (minus)
Specifies a negative interval value.
quantity
Integer numeric constant
[subtype-unit](/en/sql-reference/language-elements/literals/datetime-literals/interval-literal/interval-subtype-units/)
See Interval subtype units for valid values. Subtype units must be specified for year-month intervals; they are optional for day-time 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:

    • 9223372036854775807 usec to -9223372036854775807 usec

    • 296533 years 3 mons 21 days 04:00:54.775807 to -296533 years -3 mons -21 days -04:00:54.775807

  • The range of an interval constant is +/– 263 – 1 microseconds.

  • In Vertica, interval fields are additive and accept large floating-point 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:

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, wk, week, wks, 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 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:

  • unit specifies a day-time or year-month subtype.

  • p specifies precision, an integer between 0 and 6.

If an interval omits an interval qualifier, the default is DAY TO SECOND(6).

Interval qualifiers are divided into two categories:

Day-time 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

Year-month interval qualifiers

YEAR
Unconstrained
MONTH
Months within year
YEAR TO MONTH
Span of years and months

Examples

See Controlling interval format.