Database Reference
In-Depth Information
5 'dd-mon-yyyy hh24:mi:ss.ff') dt2
6 from dual )
7 /
DT2-DT1
---------------------------------------------------------------------------
+000000380 10:20:29.878000000
We can use
EXTRACT
to see how easy it is to pull out each bit of information:
EODA@ORA12CR1> select extract( day from dt2-dt1 ) day,
2 extract( hour from dt2-dt1 ) hour,
3 extract( minute from dt2-dt1 ) minute,
4 extract( second from dt2-dt1 ) second
5 from (select to_timestamp('29-feb-2000 01:02:03.122000',
6 'dd-mon-yyyy hh24:mi:ss.ff') dt1,
7 to_timestamp('15-mar-2001 11:22:33.000000',
8 'dd-mon-yyyy hh24:mi:ss.ff') dt2
9 from dual )
10 /
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
380 10 20 29.878
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:
EODA@ORA12CR1> select numtoyminterval(5,'year')+numtoyminterval(2,'month') from dual;
NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH')
---------------------------------------------------------------------------
+000000005-02
