Rounding dates
Posted: July 10, 2007 Filed under: dates, Oracle, sql 3 CommentsWe 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)
Just a simple Hadoop DBA 
Very nice solution. Thanks!
[…] 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. […]
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.