Controlling interval format

Interval qualifiers specify a range of options that Vertica uses to interpret and format an interval literal. The interval qualifier can also specify precision. Each interval qualifier is composed of one or two units:

unit[p] [ TO unit[p] ]

where:

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

• p specifies precision, an integer between 0 and 6. In general, precision only applies to SECOND units. The default precision for SECOND is 6. For details, see Specifying interval precision.

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

Interval qualifier categories

Interval qualifiers belong to one of the following categories:

• Year-month: Span of years and months

• Day-time: Span of days, hours, minutes, seconds, and fractional seconds

Year-Month

Vertica supports two year-month subtypes: YEAR and MONTH.

In the following example, YEAR TO MONTH qualifies the interval literal 1 2 to indicate a span of 1 year and two months:

=> SELECT interval '1 2' YEAR TO MONTH;
 ?column?
----------
 1-2
(1 row)

If you omit the qualifier, Vertica uses the default interval qualifier DAY TO SECOND and returns a different result:

=> SELECT interval '1 2';
 ?column?
----------
 1 02:00
(1 row)

The following example uses the interval qualifier YEAR. In this case, Vertica extracts only the year from the interval literal 1y 10m :

=> SELECT INTERVAL '1y 10m' YEAR;
 ?column?
----------
 1
(1 row)

In the next example, the interval qualifier MONTH converts the same interval literal to months:

=> SELECT INTERVAL '1y 10m' MONTH;
 ?column?
----------
 22
(1 row)

Day-time

Vertica supports four day-time subtypes: DAY, HOUR, MINUTE, and SECOND.

In the following example, the interval qualifier DAY TO SECOND(4) qualifies the interval literal 1h 3m 6s 5msecs 57us. The qualifier also sets precision on seconds to 4:

=> SELECT INTERVAL '1h 3m 6s 5msecs 57us' DAY TO SECOND(4);
   ?column?
---------------
 01:03:06.0051
(1 row)

If no interval qualifier is specified, Vertica uses the default subtype DAY TO SECOND(6), regardless of how you specify the interval literal. For example, as an extension to SQL:2008, both of the following commands return 910 days:

=> SELECT INTERVAL '2-6';
    ?column?
-----------------
 910
=> SELECT INTERVAL '2 years 6 months';
 ?column?
-----------------
 910

An interval qualifier can extract other values from the input parameters. For example, the following command extracts the HOUR value from the interval literal 3 days 2 hours:

=> SELECT INTERVAL '3 days 2 hours' HOUR;
 ?column?
----------
 74

The primary day/time (DAY TO SECOND) and year/month (YEAR TO MONTH) subtype ranges can be restricted to more specific range of types by an interval qualifier. For example, HOUR TO MINUTE is a limited form of day/time interval, which can be used to express time zone offsets.

=> SELECT INTERVAL '1 3' HOUR to MINUTE;
 ?column?
---------------
 01:03

hh:mm:ss and hh:mm formats are used only when at least two of the fields specified in the interval qualifier are non-zero and there are no more than 23 hours or 59 minutes:

=> SELECT INTERVAL '2 days 12 hours 15 mins' DAY TO MINUTE;
  ?column?
--------------
 2 12:15
=> SELECT INTERVAL '15 mins 20 sec' MINUTE TO SECOND;
 ?column?
----------
 15:20
=> SELECT INTERVAL '1 hour 15 mins 20 sec' MINUTE TO SECOND;
 ?column?
-----------------
 75:20