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

5 years of fun helping others


Last week was the 5 year anniversary of answering my first question on Experts Exchange and on Monday of this week I answered my 6000th question. With coincidental milestones like that it seems like a good time to reflect on what I’ve accomplished since I started participating. In that time I’ve also published 13 articles, recorded a video tutorial and participated in two conferences at EE headquarters. In 2009 I was asked to be a Zone Advisor and most recently I was inducted into the inaugural class of “Most Value Experts.”

Along with the accolades I’ve had the opportunity to work with some really stellar people; both technically and socially. My friendship and rivalry with fellow volunteer slightwv was so well known we were invited to participate in a head-to-head “Throwdown” to see who could accumulate the most points in one week. I lost… by a lot, but that was expected (at least by me.) While I have a pretty good track record, I’ve never had his speed and tenacity; it was still fun though.

I’ve had the pleasure of working with some great experts, both online and in person, from around the world including members from England, Luxembourg, Australia, Germany and all over the US. I’ve also, unfortunately, felt sadness at the loss of a long time member and frequent participant who lost his life to cancer. I met him and his wife at my first EE conference in San Luis Obispo, CA. While I can’t say we grew close from that brief meeting, I was still shocked and saddened to hear of his passing and EE misses his passionate contributions.

Friends, family and coworkers ask why I do it. People pay good money for Oracle consultants and I here I help for free. I get an occasional t-shirt or other gift from EE but I haven’t been paid a dime for any of those 6000 answers. The simple answer is: it’s fun. I get a kick out of helping people figure out their problems, especially when they are thankful for my efforts. When someone responds with “Wow! That’s amazing! I didn’t know that was possible” or “Thank you, thank you, that just saved me weeks of work!” it makes it all worthwhile.

On a less magnanimous note, I’ll admit it. It’s also fun to show off my skills when I come up with something clever. 🙂 A lot of the questions asked are pretty easy and don’t require much flexing of tech-muscle; but some of the questions are tricky and I’m proud of the solutions I came up with.

Sometimes it’s great to take a question that I don’t know how to answer just for the exercise of figuring it out. I do a lot of XML processing; but I answered a ton of questions on EE before my real job required much of it. When a project did come along though, I jumped right in with several options largely due to my exposure to multiple problems on EE.

In the last 5 years I’ve also started presenting at large Oracle conferences like Collaborate (IOUG) and KScope (ODTUG). All of the material I’ve presented so far is based on question trending I’ve seen on EE. When I see the same questions asked dozens or even hundreds of times, clearly it’s an area of instruction where developers and administrators need help. Thus my presentations were born. I’m currently getting my abstracts together for next year’s conferences and again what I’ve seen on EE will be driving the bulk of my submissions.

Experts Exchange gets a lot of grief from some people but I’ve had overwhelmingly successful and pleasant experiences with the staff and other members. I won’t say it’s 100% perfect. There are some things I’ve reported to HQ that I wish were different and I have butted heads with a couple people over the years; but those are a tiny, tiny minority of all my interactions.

My level of participation waxes and wanes as other responsibilities and interests occupy my time but I keep coming back to EE and plan to continue to do so for the foreseeable future. It’s too fun working with others, too rewarding helping people and too valuable as a resource to walk away.

If you’re ever in need of tech help, I recommend giving it a try. If your question is in Oracle, there is a good chance I’ll see it and, if I’m able, I’ll try to get you an answer.

%d bloggers like this: