Rounding dates

We are shutting down most of the SQL Servers, moving our applications and customers to Oracle. This move has been going on for a while and is targeted to finish on the end of the month. One of the applications that will have to move is our SLA calculations. Thats an important and highly visible app – our executives are following the results, so we really don’t want to mess up with this one. The downside is that moving it means translating a bunch of code written in T-SQL for SQL Server to Oracle’s PL/SQL. To make things even better, the application involves a lot of date manipulations. SQL Server and Oracle have very different approaches to dates. So, this task has been tossed back and forth for a while now, because no one really wanted to get his hands dirty with this, until it firmly lended on my desk, with 2 days deadline.

So, one of the things that had to be translated was a query that groups the last day of data into 15 minute intervals. I spent few minutes trying to translate the code into Oracle in a operation by operation basis, before I gave up and decided to write it from scratch. Since my solution is both generic (will work on any minute interval, not just 15) and much cuter than the other solutions I found in various forums, I decided to post it here:

Round to previous 15 minute (i.e. 4:10 will become 4:00):
TRUNC(sysdate,'HH24') + (to_char(SYSDATE,'mi')-mod(TO_char(SYSDATE,'mi'),15))/(24*60)

Round to next 15 minutes (4:10 will become 4:15):
trunc(sysdate,'HH24') + (to_char(SYSDATE,'mi')+15-mod(TO_char(SYSDATE,'mi'),15))/(24*60)

Advertisements

3 Comments on “Rounding dates”

  1. Yuan says:

    Very nice solution. Thanks!

  2. […] That was when I published my thoughts on OOW, which is my most popular post ever. I can never guess why some of my posts become so popular, while other good posts are barely read. You can see my popular posts on the sidebar. My favorite post that almost no one ever read was about rounding dates. […]

  3. Yuan says:

    I really appreciated this when I needed it so I figured I’d give something back. I found a pretty powerful way to aggregate dates. I write a pipelined function that takes in a date range and an interval size. It returns one row for every interval. You can just join the results of that to whatever data you’re aggregating then group by the intervals.

    FUNCTION Get_Intervals_Piped(pidFrom DATE, pidTo DATE, pinLength INTEGER, pivUnit VARCHAR2, pivTZR DST_TZD.TZR%TYPE := NULL) RETURN gtTSTbl PIPELINED IS

    lv_PROG_NM CONSTANT VARCHAR2(4000) := ‘Get_Intervals_Piped’;
    lvParams VARCHAR2(4000) := ‘pidFrom: ‘ || Pkg_Datetime_Util.Get_Dt_Str(pidFrom) || ‘; ‘ ||
    ‘pidTo: ‘ || Pkg_Datetime_Util.Get_Dt_Str(pidTo) || ‘; ‘ ||
    ‘pinLength: ‘ || pinLength || ‘; ‘ ||
    ‘pivUnit: ‘ || pivUnit || ‘; ‘ ||
    ‘pivTZR: ‘ || pivTZR;
    lts TIMESTAMP WITH TIME ZONE;
    ltsTo TIMESTAMP WITH TIME ZONE;

    BEGIN

    IF pivTZR IS NULL THEN
    lts := CAST(pidFrom AS TIMESTAMP WITH TIME ZONE);
    ltsTo := CAST(pidTo AS TIMESTAMP WITH TIME ZONE);
    ELSE
    lts := FROM_TZ(CAST(pidFrom AS TIMESTAMP), pivTZR);
    ltsTo := FROM_TZ(CAST(pidTo AS TIMESTAMP), pivTZR);
    END IF;

    WHILE lts < ltsTo LOOP

    PIPE ROW(lts);

    IF pivUnit IN ('YEAR', 'MONTH') THEN
    lts := lts + NUMTOYMINTERVAL(pinLength, pivUnit);
    ELSE
    lts := lts + NUMTODSINTERVAL(pinLength, pivUnit);
    END IF;

    END LOOP;

    RETURN;

    EXCEPTION

    WHEN OTHERS THEN
    PKG_SDBA_Error_Log.SP_Log_Error(gv_PACKAGE_NM, lv_PROG_NM, SQLCODE, SQLERRM, lvParams);
    RAISE;

    END Get_Intervals_Piped;

    This is a function in a bigger package so a lot of the references are undefined, but I'm sure you get the picture. I like this because you can reuse the same query regardless of what interval size you choose since it's just a parameter.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s