INTERVAL data type

The INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes: year-month intervals and day-time intervals.

A year-month interval can represent a span of years and months, and a day-time interval can represent a span of days, hours, minutes, seconds, and fractions of a second.

An INTERVAL value is always composed of one value or a series of values that represents time units. Within a data-definition statement such as CREATE TABLE or ALTER TABLE that defines the precision of an INTERVAL data type, the qualifiers must have the following format: 
INTERVAL largest_qualifier(n) TO smallest_qualifier

Here the largest_qualifier and smallest_qualifier keywords are taken from one of the two INTERVAL classes, as shown in the table Interval Classes.

If SECOND (or a larger time unit) is the largest_qualifier, the declaration of an INTERVAL data type can optionally specify n, the precision of the largest time unit (for n ranging from 1 to 9); this is not a feature of DATETIME data types.

If smallest_qualifier is FRACTION, you can also specify a scale in the range from 1 to 5. For FRACTION TO FRACTION qualifiers, the upper limit of n is 5, rather than 9. There are two incommensurable classes of INTERVAL data types:
  • Those with a smallest_qualifier larger than DAY
  • Those with a largest_qualifier smaller than MONTH
Table 1. Interval Classes
Interval Class Time Units Valid Entry
YEAR-MONTH INTERVAL YEAR A number of years
YEAR-MONTH INTERVAL MONTH A number of months
DAY-TIME INTERVAL DAY A number of days
DAY-TIME INTERVAL HOUR A number of hours
DAY-TIME INTERVAL MINUTE A number of minutes
DAY-TIME INTERVAL SECOND A number of seconds
DAY-TIME INTERVAL FRACTION A decimal fraction of a second, with up to 5 digits. The default scale is 3 digits (thousandth of a second). To specify a non-default scale, write FRACTION(n), where 1 < n < 5.

As with DATETIME data types, you can define an INTERVAL to include only the subset of time units that you need. But because the construct of “month” (as used in calendar dates) is not a time unit that has a fixed number of days, a single INTERVAL value cannot combine months and days; arithmetic that involves operands of the two different INTERVAL classes is not supported.

A value entered into an INTERVAL column need not include the full range of time units that were specified in the data-type declaration of the column. For example, you can enter a value of HOUR TO SECOND precision into a column defined as DAY TO SECOND. A value must always consist, however, of contiguous time units. In the previous example, you cannot enter only the HOUR and SECOND values; you must also include MINUTE values.

A valid INTERVAL literal contains the INTERVAL keyword, the values to be entered, and the field qualifiers. (See the discussion of literal intervals in the HCL OneDB™ Guide to SQL: Syntax.) When a value contains only one field, the largest and smallest fields are the same.

When you enter a value in an INTERVAL column, you must specify the largest and smallest fields in the value, just as you do for DATETIME values. In addition, you can optionally specify the precision of the first field (and the scale of the last field if it is a FRACTION). If the largest and smallest field qualifiers are both FRACTION, you can specify only the scale in the last field.

Acceptable qualifiers for the largest and smallest fields are identical to the list of INTERVAL fields that the tab;e Interval Classes displays.

If you use the DB-Access TABLE menu, but you specify no INTERVAL field qualifiers, then a default INTERVAL qualifier, YEAR TO YEAR, is assigned.

The largest_qualifier in an INTERVAL value can be up to nine digits (except for FRACTION, which cannot be more than five digits), but if the value that you want to enter is greater than the default number of digits allowed for that field, you must explicitly identify the number of significant digits in the value that you enter. For example, to define an INTERVAL of DAY TO HOUR that can store up to 999 days, you can specify it the following way: 
INTERVAL DAY(3) TO HOUR

INTERVAL literals use the same delimiters as DATETIME literals (except that MONTH and DAY time units are not valid within the same INTERVAL value). the following table shows the INTERVAL delimiters.

Table 2. INTERVAL Delimiters
Delimiter Placement in an INTERVAL Literal
Hyphen Between the YEAR and MONTH portions of the value
Blank space Between the DAY and HOUR portions of the value
Colon Between the HOUR, MINUTE, and SECOND portions of the value
Decimal point Between the SECOND and FRACTION portions of the value
You can also enter INTERVAL values as character strings. The character string must include information for the same time units that were specified in the data-type declaration for the column. The INSERT statement in the following example shows an INTERVAL value entered as a character string: 
INSERT INTO manufact (manu_code, manu_name, lead_time)
   VALUES ('BRO', 'Ball-Racquet Originals', '160')

Because the lead_time column is defined as INTERVAL DAY(3) TO DAY, this INTERVAL value requires only one field, the span of days required for lead time. If the character string does not contain information for all fields (or adds additional fields), the database server returns an error. For additional information about entering INTERVAL values as character strings, see the HCL OneDB Guide to SQL: Syntax.

By default, all fields of an INTERVAL column are two-digit numbers, except for the year and fraction fields. The year field is stored as four digits. The fraction field requires n digits where 1 < n < 5, rounded up to an even number. You can use the following formula (rounded up to a whole number of bytes) to calculate the number of bytes required for an INTERVAL value: 
(total number of digits for all fields)/2 + 1

For example, INTERVAL YEAR TO MONTH requires six digits (four for year and two for month), and requires 4, or (6/2) + 1, bytes of storage.

For information about using INTERVAL as a constant expression, see the description of the INTERVAL Field Qualifier in the HCL OneDB Guide to SQL: Syntax.