Close

Filling in missing functionality with Oracle Interval types

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);