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 can have varying 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; 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. |
This section contains the following topics.