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.
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)
```
For details, see Specifying interval precision.

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

Interval qualifiers are divided into two categories:

day-time
year-month

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

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?
----------
 1-2
(1 row)
=> SELECT INTERVAL '1 2' YEAR TO MONTH;
?column?
1-2
(1 row)

Examples

See Controlling interval format.