Oracle supports several methods for constructing Interval types including the NUMTODSINTERVAL and NUMTOYMINTERVAL functions.
As their names imply, these simply convert a number of some units (day,hour,minute, second or month,year respectively) into an interval type. There is however no built-in method for reversing the process.
The two functions below fill in this hole. I hope you find them helpful.
CREATE OR REPLACE FUNCTION dsintervaltonum( p_interval IN INTERVAL DAY TO SECOND, p_unit IN VARCHAR2 ) RETURN NUMBER DETERMINISTIC IS -- .///. -- (0 o) ---------------0000--(_)--0000--------------- -- -- Sean D. Stuber -- sean.stuber@gmail.com -- -- oooO Oooo --------------( )-----( )--------------- -- \ ( ) / -- \_) (_/ illegal_argument EXCEPTION; PRAGMA EXCEPTION_INIT(illegal_argument, -1760); BEGIN CASE UPPER(p_unit) WHEN 'SECOND' THEN RETURN EXTRACT(DAY FROM p_interval) * 86400 + EXTRACT(HOUR FROM p_interval) * 3600 + EXTRACT(MINUTE FROM p_interval) * 60 + EXTRACT(SECOND FROM p_interval); WHEN 'MINUTE' THEN RETURN EXTRACT(DAY FROM p_interval) * 1440 + EXTRACT(HOUR FROM p_interval) * 60 + EXTRACT(MINUTE FROM p_interval) + EXTRACT(SECOND FROM p_interval) / 60; WHEN 'HOUR' THEN RETURN EXTRACT(DAY FROM p_interval) * 24 + EXTRACT(HOUR FROM p_interval) + EXTRACT(MINUTE FROM p_interval) / 60 + EXTRACT(SECOND FROM p_interval) / 3600; WHEN 'DAY' THEN RETURN EXTRACT(DAY FROM p_interval) + EXTRACT(HOUR FROM p_interval) / 24 + EXTRACT(MINUTE FROM p_interval) / 1440 + EXTRACT(SECOND FROM p_interval) / 86400; ELSE RAISE illegal_argument; END CASE; END dsintervaltonum;
CREATE OR REPLACE FUNCTION ymintervaltonum( p_interval IN INTERVAL YEAR TO MONTH, p_unit IN VARCHAR2 ) RETURN NUMBER DETERMINISTIC IS -- .///. -- (0 o) ---------------0000--(_)--0000--------------- -- -- Sean D. Stuber -- sean.stuber@gmail.com -- -- oooO Oooo --------------( )-----( )--------------- -- \ ( ) / -- \_) (_/ illegal_argument EXCEPTION; PRAGMA EXCEPTION_INIT(illegal_argument, -1760); BEGIN CASE UPPER(p_unit) WHEN 'MONTH' THEN RETURN EXTRACT(MONTH FROM p_interval) + EXTRACT(YEAR FROM p_interval) * 12; WHEN 'YEAR' THEN RETURN EXTRACT(MONTH FROM p_interval) / 12 + EXTRACT(YEAR FROM p_interval); ELSE RAISE illegal_argument; END CASE; END ymintervaltonum;
Sample usage:
SELECT dsintervaltonum(dy, 'hour') hours,
ymintervaltonum(ym, 'month') months
FROM
(SELECT INTERVAL '7 2:07:38' DAY TO SECOND(9) dy,
TO_YMINTERVAL('3-1') ym
FROM DUAL);