Interval value expressions

Interval value expressions are similar to timestamp expressions, and represent one more option for converting character strings to interval values. They are also more flexible than the TO_xxINTERVAL family of functions.

Interval value expressions take on the following form:

INTERVAL 'character_representation' start_element TO end_element

where:

character_representation

Is the character string representation of the interval. See "The TO_xxINTERVAL functions" for a description of how the two interval data types are represented in character form.

start_element

Specifies the leading element in the interval.

end_element

Specifies the trailing element in the interval.

Unlike the TO_xxINTERVAL functions, interval expressions allow you to specify an interval using any sequence of datetime elements from Table 10-2. There are only two restrictions:

· You must use a consecutive sequence of elements

· You cannot transition from a month to a day within the same interval

Following are several valid examples:

DECLARE A INTERVAL YEAR TO MONTH; B INTERVAL YEAR TO MONTH; C INTERVAL DAY TO SECOND; D INTERVAL DAY TO SECOND;BEGIN /* Some YEAR TO MONTH examples */ A := INTERVAL '40-3' YEAR TO MONTH; B := INTERVAL '40' YEAR; /* Some DAY TO SECOND examples */ C := INTERVAL '10 1:02:10.123' DAY TO SECOND; /* Fails in Oracle9i, Release 1 because of a bug */ --D := INTERVAL '1:02' HOUR TO MINUTE; /* Following are two workarounds for defining intervals, such as HOUR TO MINUTE, that represent only a portion of the DAY TO SECOND range. */ SELECT INTERVAL '1:02' HOUR TO MINUTE INTO D FROM dual; D := INTERVAL '1' HOUR + INTERVAL '02' MINUTE;END;

In Oracle9i Releases 1 and 2, the expression INTERVAL `1:02' HOUR TO MINUTE will work from a SQL statement, but not from a PL/SQL statement. Furthermore, you'll get an error about using the keyword BULK in the wrong context. This is now a known bug that we expect to see fixed in a future release.

Because there is no one interval type that includes both month and day, any interval that attempts to include both is invalid. For example, the following interval expression will generate an error:

INTERVAL '1-15' MONTH TO DAY

Likewise, there is no mechanism for skipping over an element. You can't, for example, specify values for days and minutes without also specifying some number of hours.

One very convenient thing that Oracle will do for you is to normalize interval values. In the following example, 72 hours and 15 minutes is normalized to 3 days, 0 hours, and 15 minutes:

DECLARE A INTERVAL DAY TO SECOND;BEGIN SELECT INTERVAL '72:15' HOUR TO MINUTE INTO A FROM DUAL; DBMS_OUTPUT.PUT_LINE(A);END;

The output is:

+03 00:15:00.000000

Oddly, Oracle will normalize only the high-end value (hours in this example). An attempt to specify an interval of 72:75 (72 hours and 75 minutes) results in an error.