INTERVAL Type-Datatypes
We briefly saw the INTERVAL type used in the previous section. It is a way to represent a duration of time or an interval of time. There are two interval types we’ll discuss in this section: the YEAR TO MONTH type, which is capable of storing a duration of time specified in years and months, and the DAY TO SECOND type, which is capable of storing a duration of time in days, hours, minutes, and seconds (including fractional seconds).
Before we get into the specifics of the two INTERVAL types, I’d like to look at the EXTRACT built-in function, which can be very useful when working with this type. The EXTRACT built-in function works on TIMESTAMPs and INTERVALs, and it returns various bits of information from them, such as the time zone from a TIMESTAMP or the hours/days/ minutes from an INTERVAL. Let’s use the previous example, where we got the INTERVAL of 380 days, 10 hours, 20 minutes, and 29.878 seconds:
SQL> select dt2-dt1
from (select to_timestamp(’29-feb-2000 01:02:03.122000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt1,to_timestamp(’15-mar-2001 11:22:33.000000′, ‘dd-mon-yyyy hh24:mi:ss.ff’) dt2from dual );DT2-DT1
We can use EXTRACT to see how easy it is to pull out each bit of information:
SQL> select extract( day from dt2-dt1 ) day,extract( hour from dt2-dt1 ) hour,extract( minute from dt2-dt1 ) minute,extract( second from dt2-dt1 ) secondfrom (select to_timestamp(’29-feb-2000 01:02:03.122000′,’dd-mon-yyyy hh24:mi:ss.ff’) dt1,to_timestamp(’15-mar-2001 11:22:33.000000′,’dd-mon-yyyy hh24:mi:ss.ff’) dt2from dual );DAY HOUR MINUTE SECOND
Additionally, we’ve already seen the NUMTOYMINTERVAL and the NUMTODSINTERVAL for creating YEAR TO MONTH and DAY TO SECOND intervals. I find these functions to be the easiest way to create instances of INTERVAL types—over and above the string conversion functions. Rather than concatenate a bunch of numbers representing the days, hours, minutes, and seconds representing some interval together, I’d rather add up four calls to NUMTODSINTERVAL to do the same.
The INTERVAL type can be used to store not just durations, but times as well in a way. For example, if you want to store a specific date and time, you have the DATE or TIMESTAMP types. But what if you want to store just the time 8:00 a.m.? The INTERVAL type would be handy for that (the INTERVAL DAY TO SECOND type in particular).
INTERVAL YEAR TO MONTH
The syntax for INTERVAL YEAR TO MONTH is straightforward:
INTERVAL YEAR(n) TO MONTH
where N is an optional number of digits to support for the number of years and varies from 0 to 9, with a default of 2 (to store a number of years from 0 to 99). It allows you to store any number of years (up to nine digits’ worth, anyway) and months. The function I prefer to use to create INTERVAL instances of this type is NUMTOYMINTERVAL. For example, to create an interval of five years and two months, we can use the following:
SQL> select numtoyminterval(5,’year’)+numtoyminterval(2,’month’) from dual;NUMTOYMINTERVAL(5,’YEAR’)+NUMTOYMINTERVAL(2,’MONTH’)
Or, using a single call and the fact that a year has 12 months, we can use the following approach:
SQL> select numtoyminterval(512+2,’month’) from dual;NUMTOYMINTERVAL(512+2,’MONTH’)
Either approach works well. Another function, TO_YMINTERVAL, can be used to convert a string into a year/month INTERVAL type:
SQL> select to_yminterval( ‘5-2’ ) from dual;TO_YMINTERVAL(‘5-2’)
But since the vast majority of the time I have the year and months in two NUMBER fields in my application, I find the NUMTOYMINTERVAL function to be more useful, as opposed to building a formatted string from the numbers. Lastly, you can just use the INTERVAL type right in SQL, bypassing the functions altogether:
SQL> select interval ‘5-2’ year to month from dual;
INTERVAL’5-2’YEARTOMONTH