Interval Qualifier

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.

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

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.