The interval types are often under utilized, even completely unknown to many developers. They can provide a wealth of functionality though when used properly.
Within the SQL context there are two types of intervals: INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH. The names indicate the maximum and minimum units of measure. That is, you can count days, hours, minutes, and seconds in the first type, or you can count years and months with the second type. There is no single interval type what would let you represent 4 years, 2 months, 3 days, and 7 hours. You would need to use two intervals, one of each type to do so.
In addition to the SQL forms, PL/SQL has an additional subtype for each form of interval: DSINTERVAL_UNCONSTRAINED and YMINTERVAL_UNCONSTRAINED. These are defined in the SYS.STANDARD package and are useful for procedure/function parameters where precision specifications are not legal syntax within a parameter declaration.
One might wonder why these data types are needed at all. Oracle already supports date/time math with ADD_MONTHS and simple numeric addition and substraction (e.g. add_months(sysdate,3) or sysdate + 2.5.) Interval types are part of the SQL 92 standard and the Oracle types are an implementation of part of that standard. Also as part of SQL92, the difference of two timestamp values is defined to be an INTERVAL. And last, the arithmetic operators for DATE values won’t preserve type if applied to a TIMESTAMP value. For example, the expression (SYSTIMESTAMP + 1) will return a DATE type even though the SYSTIMESTAMP function returns a TIMESTAMP WITH TIME ZONE type. Intervals can be added to or subtracted from the various TIMESTAMP types as well as DATEs while preserving the same type.
In the next two articles I will dig into the details of each interval type; but before doing that… what is an INTERVAL? An interval is a duration; but not of any particular point in time. That is, an INTERVAL can hold a value of 4 hours and 36 minutes, but it can not represent the period of time between 3:07pm and 7:43pm of a day. Similarly, an INTERVAL might represent one year and three months; but could not reflect the period from March 2010 to June 2011 You would need two date/time values, or one date/time plus an interval to represent a specific period of time.
Since INTERVALS do not represent any particular period, there is no concept of time zone or daylight saving time within an interval. Thus one day within an interval is always 24 hours. It should also be noted there is no conversion or compatibility between the two INTERVAL types. Unlike the various numeric text types which have implicit compatibility (with caveats) between each other or even DATE and TIMESTAMP types which can be assigned to each other (again, with caveats.) The INTERVAL types have no such relation, if you try you will raise a PLS-382 or ORA-932 exception, depending on SQL vs PL/SQL context.
SQL> DECLARE
2 i INTERVAL YEAR TO MONTH;
3 BEGIN
4 i := INTERVAL '30' DAY;
5 END;
6 /
i := INTERVAL '30' DAY;
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00382: expression is of wrong type
SQL> create table t (ym interval year to month, ds interval day to second);
Table created.
SQL> insert into t (ym) values (INTERVAL '30' DAY);
insert into t (ym) values (INTERVAL '30' DAY)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected INTERVAL YEAR TO MONTH got INTERVAL
DAY TO SECOND
SQL> insert into t (ds) values (INTERVAL '1' MONTH);
insert into t (ds) values (INTERVAL '1' MONTH)
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got INTERVAL
YEAR TO MONTH
One of the more obnoxious features (or rather, lack of features) with INTERVAL is formatting. DATE and TIMESTAMP types which have TO_CHAR formatting rules as well as defaults defined by various NLS session settings. INTERVALs though do not, at least not a documented format. If you query INTERVAL values in SQL*PLUS, sqlcl, or otherwise force an implicit conversion to text, they will appear in the following format.
SQL> SELECT INTERVAL '123456789-11' YEAR(9) TO MONTH ym, 2 INTERVAL '123456789 00:00:00.123456789' DAY(9) to SECOND(9) ds 3 FROM DUAL; YM DS -------------- ------------------------------ +123456789-11 +123456789 00:00:00.123456789
There are no options to specify different whitespace, delimiters, trimming, sign, etc. Furthermore, while this format has been consistent since their introduction in 9i, they do not have a documented format. In fact, since 10gR2 the SQL Reference specifically states: “Interval data types do not have format models.”
So, even though the formats above have been reliable; if you want a fully supported text representation you should use the EXTRACT function to pull the various components out of the INTERVAL value and construct the text yourself in the format you need. Something like the concatentions below would produce the formats above while using only documented functionality.
--- Reconstructing the undocumented default day to second format CASE WHEN p_interval < INTERVAL '0' SECOND THEN '-' ELSE '+' END || TO_CHAR(ABS(EXTRACT(DAY FROM p_interval)), 'fm999999999') || ' ' || TO_CHAR(ABS(EXTRACT(HOUR FROM p_interval)), 'fm00') || ':' || TO_CHAR(ABS(EXTRACT(MINUTE FROM p_interval)), 'fm00') || ':' || TO_CHAR(ABS(EXTRACT(SECOND FROM p_interval)), 'fm00.999999999') --- Reconstructing the undocumented default year to month format CASE WHEN p_interval < INTERVAL '0' MONTH THEN '-' ELSE '+' END || TO_CHAR(ABS(EXTRACT(YEAR FROM p_interval)), 'fm999999999') || '-' || TO_CHAR(ABS(EXTRACT(MONTH FROM p_interval)), 'fm00')
I use the CASE and ABS() instead of the “S” format model because a 0 value is considered positive, so extracting a 0 DAY/YEAR from a negative interval would be written as “+0” if using just the “S” model, and if I includes the “S” format in the items after the DAY/YEAR it would not follow the correct format since month,hour,minute,second would each have a negative sign, hence the ABS() call.
You can, of course, choose other formats as well once you have the individual components of an interval you can convert and concatenate them any way you wish.
And finally, I’ve discussed INTERVAL types previously, providing functions to extract a single numeric value representing the number of user-selected units within an interval. Using the functions defined in this article you can, for example, extract the total number of hours or seconds from an INTERVAL DAY TO SECOND or the number of months from an INTERVAL YEAR TO MONTH.
The following articles will dig into the specifics of each INTERVAL type.