Computing the interval between two datetimes

If you compute the interval between two DATE values, the result is the number of 24-hour periods (not the same as days) between the two values. If the number was an integer, then the difference was an exact number of days. If the number was a fractional number, then the difference included some number of hours, minutes, and seconds as well.

A significant drawback to the old mechanism for computing intervals is that you get fractional numbers as a result. Look at the following example, which you may remember from the previous section:

BEGIN DBMS_OUTPUT.PUT_LINE ( TO_DATE('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am') - TO_DATE('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am') );END;

The output is:

149.001388888888888888888888888888888889

The 149 days you can understand, but you probably wonder what exactly is represented by .001388888888888888888888888888888889. How many hours is that? You might be able to work out the corresponding number of hours, minutes, and seconds, but you might not get an accurate result, especially with that repeating decimal in the value. Oracle9i to the rescue! The new INTERVAL types make it easy to compute an interval in terms that make sense to us humans. For example:

DECLARE a INTERVAL DAY(3) TO SECOND(0);BEGIN --Compute interval and assign to an INTERVAL DAY TO SECOND variable a := TO_TIMESTAMP('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am') - TO_TIMESTAMP('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am'); DBMS_OUTPUT.PUT_LINE(a);END;

The output is:

+149 00:02:00

In this example, we subtract one date from the other and assign the resulting difference to a variable of the INTERVAL DAY TO SECOND type. Instead of a number with a repeating fraction that we can't easily understand, the result is an interval that can be expressed exactly as 149 days, zero hours, two minutes, and zero seconds.

Values of the INTERVAL types can be returned only from subtractions involving the new TIMESTAMP types.

Oddly, or seemingly so, PL/SQL doesn't seem to support the return of an INTERVAL YEAR TO MONTH value as the difference between two timestamps. Thus, neither of the calculations for "c" in the following block will be successful:

DECLARE a TIMESTAMP; b TIMESTAMP; c INTERVAL YEAR TO MONTH;BEGIN a := TO_TIMESTAMP('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am'); b := TO_TIMESTAMP('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am'); c := a - b; c := CAST(a - b AS INTERVAL YEAR TO MONTH);END;

Regardless of whether we use the CAST operator, we can't compute the difference between two timestamps as an INTERVAL YEAR TO MONTH. Oracle provides no official explanation for this—in fact, the documentation doesn't even mention that you cannot perform such a calculation. After some thought we've come up with the following explanation:

1. Any given expression yields a result in a defined datatype. In the case of subtracting two timestamps, the resulting datatype is always INTERVAL DAY TO SECOND.

2. The result type of an expression can't depend on the type of the variable to which it is assigned. PL/SQL can't look at the left side of the assignment operator and change the semantics of the expression on the right side.

3. When the result of an expression is of a type not matching the type of the target variable on the left side of the assignment operator, PL/SQL performs an implicit conversion.

4. An implicit conversion from INTERVAL DAY TO SECOND to INTERVAL YEAR TO MONTH is not possible, because the number of days in a month varies from month to month. Fundamentally, this is the same reason for not allowing an INTERVAL datatype to span MONTH and DAY to begin with (see Section 10.1.3 earlier in this chapter).

Intervals can be negative or positive. So far, all the intervals we've shown have been positive. In the context of subtraction, a positive interval indicates that you've subtracted a date in the past from a more recent date. For example:

15-Nov-1961 - 18-Jun-1961 = +150

A negative interval indicates that you've subtracted a more recent date from a date further in the past, as in:

18-Jun-1961 - 15-Nov-1961 = -150

Fundamentally, the sign of the result indicates the directionality of the interval; we'll talk more about that in the upcoming section Section 10.3.2.4. It's somewhat unfortunate that there's no absolute value function that applies to intervals in the same way that the ABS function applies to numeric values.