NUMTODSINTERVAL and NUMTOYMINTERVAL
NUMTODSINTERVAL(n, unit) and
NUMTOYMINTERVAL(n, unit) convert a number (
n) to a
interval day to second and
interval year to month, respectively.
column dm_day format a29
column dm_hour format a29
column dm_minute format a29
column dm_second format a29
column ds_year format a13
column ds_month format a13
with num as (
select 42 ber from dual union all
select 1.5 ber from dual union all
select - 3.75 ber from dual
)
select
num.ber,
numtodsinterval(num.ber, 'day' ) dm_day,
numtodsinterval(num.ber, 'hour' ) dm_hour,
numtodsinterval(num.ber, 'minute') dm_minute,
numtodsinterval(num.ber, 'second') dm_second,
numtoyminterval(num.ber, 'year' ) ds_year,
numtoyminterval(num.ber, 'month' ) ds_month
from
num;
--
-- BER DM_DAY DM_HOUR DM_MINUTE DM_SECOND DS_YEAR DS_MONTH
------------ ----------------------------- ----------------------------- ----------------------------- ----------------------------- ------------- -------------
-- 42 +000000042 00:00:00.000000000 +000000001 18:00:00.000000000 +000000000 00:42:00.000000000 +000000000 00:00:42.000000000 +000000042-00 +000000003-06
-- 1.5 +000000001 12:00:00.000000000 +000000000 01:30:00.000000000 +000000000 00:01:30.000000000 +000000000 00:00:01.500000000 +000000001-06 +000000000-02
-- -3.75 -000000003 18:00:00.000000000 -000000000 03:45:00.000000000 -000000000 00:03:45.000000000 -000000000 00:00:03.750000000 -000000003-09 -000000000-04
EXTRACT
EXTRACT(unit FROM expr) returns the number of units of an interval (or datetime) expression.
Note that unit, unlike in NUMTODSINTERVAL and NUMTOYMINTERVAL, must not be quoted.
Note also that EXTRACT returns only the part that belongs to the unit, the following example returns 5, not 65 or 65.2:
select
extract(minute from numtodsinterval(1.2, 'minute'))
from
dual;
TO_DSINTERVAL and TO_YMINTERVAL
TO_DSINTERVAL and TO_YMINTERVAL convert a string argument in SQL interval or ISO 8601:2004 format to an interval.
The SQL interval format looks like so
dd hh:mi:ss
dd hh:mi:ss.fff
+dd hh:mi:ss.fff
-dd hh:mi:ss.fff
etc.
The ISO 8601 format starts with P or -P.
The number of days (if specified) is followed by a D.
If either of hours, minutes or seconds are specified, a T follows the D or the P.
Hours, if any, are followed by an H, minutes, if any, by an M and (fractional) seconds, if any, by an S.
select
to_dsinterval('12 5:13:28.03' ) sql_format, -- 12 days, 5 hours, 13 minutes, 28.03 seconds
to_dsinterval('P12DT5H13M28.03S') iso_format, -- same thing, but ISO format
to_dsinterval('P18D' ) iso_18_days, -- exactly 18 days
to_dsinterval('PT12S' ) iso_12_seconds -- exactly 12 seconds
from
dual;