Interval datatypes in Oracle

Starting from Oracle 9i Release 1, there are datatypes available in Oracle which can store period intervals. The differences or intervals can be specified in years, months, days, hours, minutes and seconds. The new datatypes where introduced to comply with SQL 99 standards. Till Oracle 9i programmers had to convert the interval to some milliseconds/seconds format and store it in NUMBER datatype.

Why do we use the INTERVAL datatypes introduced? We already have DATE datatype which can store DATE and TIME information. The answer is that the INTERVAL datatype stores an interval of time and not exactly one time at one point of time. Isn’t this a good feature?

In Oracle 9i, the time interval datatypes INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND were added along with several other datatypes to deal more precisely with points in time. The TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE datatypes all express time to fractions of a second, and the last two accounts for changes geographical location as well.

You can use the interval datatypes in both SQL and PL/SQL. They are specified the same way:

INTERVAL YEAR [(year_precision)] TO MONTH

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

There are defaults for the precision values: two digits for the year and day, and six digits for fractions of seconds. Interval literals are expressed by the word INTERVAL, followed by an expression in single quotes, and words that interpret the expression. YEAR TO MONTH interval literals use a hyphen (-) between the year and month. DAY TO SECOND interval literals use a space between the number of days and time.

For example, this is a time interval of two years and six months:

INTERVAL '2-6' YEAR TO MONTH

This covers three days, 12 hours, 30 minutes, and 6.7 seconds:

INTERVAL '3 12:30:06.7' DAY TO SECOND (1)

Intervals can be positive or negative. They can be added to or subtracted from the various TIMESTAMP datatypes; the result is a new TIMESTAMP. They can be added or subtracted from each other as well, resulting in a new interval.

The following example shows how to create a table to record the time interval, for instance for a test.

CREATE TABLE test
(test_id NUMBER(9),
test_desc VARCHAR2(80),
test_start TIMESTAMP,
test_duration INTERVAL DAY(1) TO SECOND(4)
);

Table created.

INSERT INTO test
VALUES (
1, 'MATHEMATICS TEST', '23-AUG-2007 02:00:00 PM',
INTERVAL '0 3:00:0' DAY(0) TO SECOND(0)
);

1 row created.

SELECT * FROM test;

TEST_ID TEST_DESC TEST_START TEST_DURATION
--------------------------------------------------
1 MATHEMATICS TEST 23-AUG-07 02.00.00.000000 PM +0 03:00:0

Now we can compute the ending time like below:

SELECT test_id, test_start,
Test_start + test_duration test_end
FROM test;

TEST_ID TEST_START TEST_END
-----------------------------------------------
1 23-AUG-07 02.00.00.000000000 PM 23-AUG-07 05.00.00.000000000 PM

Unfortunately, the TO_CHAR function doesn't contain any format models that map to the individual pieces of interval datatypes. Instead, you can use the new EXTRACT function to extract pieces and combine them. The syntax is as below:

EXTRACT(timepart FROM interval_expression)

The example for the usage of EXTRACT function is as follows:

SELECT EXTRACT(DAY FROM test_duration) ||
      ' days, ' || EXTRACT (HOUR FROM test_duration) ||
      ' hours, ' || EXTRACT (MINUTE FROM test_duration) ||
      ' minutes' Duration
FROM test;

DURATION
--------------------------
0 days, 3 hours, 0 minutes

First, the number of days is extracted from the column test_duration, and the literal "Days" is concatenated to it. The same is done for the hours and minutes portions of the test’s duration.