Edit

Interval Data Types

  • Article

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.