Interval Arithmetic

Oracle9i introduces the concept of an INTERVAL datatype, leading to a whole new level of complexity when it comes to date arithmetic. But the concept of an interval isn't really new. If you've been doing date arithmetic in older releases of Oracle, then you've already been working with intervals. Date arithmetic involving numbers, as described in the previous section, is nothing more than date arithmetic using intervals expressed in terms of days and fractions of a day. Think back to the problem of adding one day to a date. In Oracle9i, you can do either of the following:

hire_date + 1; hire_date + INTERVAL '1' DAY;

See, we've been working with intervals all along. Oracle has just now formalized the concept, and in the process has given us two more interval types to work with. Beginning in Oracle9i, you can express intervals in any of the following terms:

INTERVAL YEAR TO MONTH type

Allows us to express intervals in terms of years and months

INTERVAL DAY TO SECOND type

Allows us to express intervals in terms of days, hours, minutes, and seconds

Numeric values

Allow us to express intervals in terms of days and fractions of a day

With this in mind, date arithmetic in Oracle9i can be reduced to the following three types of operations:

· Adding or subtracting an interval to or from a datetime value

· Subtracting one datetime value from another in order to determine the interval between the two values

· Adding or subtracting one interval to or from another interval

The only thing that's really new here is the terminology and the INTERVAL datatypes. Don't let these intimidate you. The first two bullets correspond to the first two bullets in the previous section; only the terminology has changed. Even the third bullet represents nothing new—we've always been able to add and subtract date intervals. It's just that pre-Oracle9i, that meant adding and subtracting numbers, whereas now, when working with the new TIMESTAMP family of types, you can add or subtract INTERVAL datatypes.

10.3.2.1 Adding and subtracting intervals to/from datetimes

The fundamental concept of adding an interval of time to a datetime value is the same in Oracle9i as in prior releases. In Oracle8i you can do:

hire_date + 1;

In Oracle9i you can do the same thing, as well as:

hire_date + INTERVAL '1' DAY;

Nothing is new here but the INTERVAL DAY TO SECOND datatype. The expression INTERVAL `1' DAY yields a value of the INTERVAL DAY TO SECOND datatype. We specified only a number of days, so hours, minutes, and seconds will default to zero. See Section 10.2.4.3 earlier in this chapter for more details on writing the kinds of interval expressions we've used here.

The following code snippet contains some more examples of interval arithmetic in Oracle9i:

DECLARE hire_date TIMESTAMP WITH TIME ZONE; a INTERVAL YEAR TO MONTH; b INTERVAL DAY TO SECOND;BEGIN hire_date := TIMESTAMP '2000-09-01 00:00:00 -5:00'; DBMS_OUTPUT.PUT_LINE(hire_date); a := INTERVAL '1-2' YEAR TO MONTH; b := INTERVAL '3 4:5:6.7' DAY TO SECOND; --Add some years and months hire_date := hire_date + a; DBMS_OUTPUT.PUT_LINE(hire_date); --Add some days, hours, minutes, and seconds hire_date := hire_date + b; DBMS_OUTPUT.PUT_LINE(hire_date);END;

The output is:

01-SEP-00 12.00.00.000000 AM -05:0001-NOV-01 12.00.00.000000 AM -05:0004-NOV-01 04.05.06.700000 AM -05:00