Date and Time Intervals

Date and time interval data are an integral part of our day-to-day life. Common examples of interval data are the age of a person, the maturity period of a bond or certificate of deposit, and the warranty period of your car. Prior to Oracle9i, we all used the NUMBER datatype to represent such data, and the logic needed to deal with interval data had to be coded at the application level. Oracle9i provides two new datatypes to handle interval data:

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

The following sections discuss the use of these datatypes.

INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH type stores a period of time expressed as a num- ber of years and months. An INTERVAL YEAR TO MONTH datatype is specified as:

INTERVAL YEAR [ (precision for year) ] TO MONTH The precision specifies the number of digits in the year field. The precision can range from 0 to 9, and the default value is 2. The default precision of two allows for a max- imum interval of 99 years, 11 months.

The following example creates a table with INTERVAL YEAR TO MONTH datatype:

CREATE TABLE EVENT_HISTORY ( EVENT_ID NUMBER(10),

EVENT_DURATION INTERVAL YEAR TO MONTH);

INTERVAL DAY TO SECOND

The INTERVAL DAY TO SECOND type stores a period of time expressed as a num- ber of days, hours, minutes, seconds, and fractions of a second. An INTERVAL DAY TO SECOND datatype is specified as:

INTERVAL DAY [(precision for day)] TO SECOND [(precision for fractional seconds)]

The precision for day specifies the number of digits in the day field. This precision can range from 0 to 9, and the default value is 2. The precision for fractional seconds is the number of digits in the fractional part of second. It can range from 0 to 9, and the default value is 6.