Interval Data Types

An interval is defined as the difference between two dates and times. Intervals are expressed in one of two different ways. One is a year-month interval that expresses intervals in terms of years and an integral number of months. The other is a day-time interval that expresses intervals in terms of days, minutes, and seconds. These two types of intervals are distinct and cannot be mixed, because months may have differing numbers of days.

An interval consists of a set of fields. There is an implied ordering among the fields. For example, in a year-to-month interval, the year comes first, followed by the month. Similarly, in a day-to-minute interval, the fields are in the order day, hour, and minute. The first field in an interval type is called the leading field, or the high-order field. The last field is called the trailing field.

In all intervals, the leading field is not constrained by rules of the Gregorian calendar. For example, in an hour-to-minute interval, the hour field is not constrained to be between 0 and 23 (inclusive), as it normally is. The trailing fields subsequent to the leading field follow the usual constraints of the Gregorian calendar. For more information, see “Constraints of the Gregorian Calendar,” later in this appendix.

There are 13 interval SQL data types and 13 interval C data types. Each of the interval C data types uses the same structure, SQL_INTERVAL_STRUCT, to contain the interval data. (For more information, see the next section, “C Interval Structure.”) For more information on the SQL data types, see “SQL Data Types” earlier in this appendix; for more information on the C data types, see “C Data Types.”

Type identifier
Class
Description

MONTH
Year-Month
Number of months between two dates.

YEAR
Year-Month
Number of years between two dates.

YEAR_
TO_MONTH
Year-Month
Number of years and months between two dates.

DAY
Day-Time
Number of days between two dates.

HOUR
Day-Time
Number of hours between two date/times.

MINUTE
Day-Time
Number of minutes between two date/times.

SECOND
Day-Time
Number of seconds between two date/times.

DAY_TO_HOUR
Day-Time
Number of days/hours between two date/times.

DAY_TO_MINUTE
Day-Time
Number of days/hours/minutes between two date/times.

DAY_TO_SECOND
Day-Time
Number of days/hours/minutes/seconds between two date/times.

HOUR_TO_MINUTE
Day-Time
Number of hours/minutes between two date/times.

HOUR_TO_SECOND
Day-Time
Number of hours/minutes/seconds between two date/times.

MINUTE_TO_SECOND
Day-Time
Number of minutes/seconds between two date/times.