Saturday, October 8, 2011

Understanding TIME INTERVAL Data Types in Oracle

Time Interval data types are part of Oracle database since Oracle 9i. Before, there is no way to store intervals between two dates in such easy way. Designers have to convert the interval into seconds and store that in a numeric format.

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are two data types added to store interval of two dates. They can be use both in SQL and PL/SQL. The syntax is as below:

INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

There are defaults for precision. Two digest for year and day and six digest for fractional seconds.

SQL>   SELECT INTERVAL '5-9' YEAR TO MONTH FROM DUAL
Result: +05-09
Cover a time interval of 5 years and 9 months

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND FROM DUAL
Result: +07 15:20:07.700000
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND(1) FROM DUAL
Result: +07 15:20:07.7
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

Intervals can be positive or negative.

Below is another example that create a table with interval data types, insert some records in it, and show up the results.

CREATE TABLE EG_TEST_INTERVAL
(
       ID NUMBER,
       TIME1 INTERVAL YEAR TO MONTH,
       TIME2 INTERVAL DAY TO SECOND
);

INSERT INTO EG_TEST_INTERVAL VALUES (1, INTERVAL '5-9' YEAR TO MONTH, INTERVAL '7 15:20:07.7' DAY TO SECOND);
INSERT INTO EG_TEST_INTERVAL VALUES (2, INTERVAL '-5-9' YEAR TO MONTH, INTERVAL '-7 15:20:07.7' DAY TO SECOND);

COMMIT;

SELECT * FROM EG_TEST_INTERVAL;

Results: 1 +05-09 +07 15:20:07.700000
             2 -05-09  -07 15:20:07.700000

No comments:

Post a Comment