Q

Mapping time intervals with Oracle's date arithmetic

I'm having problems mapping time intervals in a time schedule. What I'm trying to do is, given a date interval

(Ex: Start Time - 19-09-2002 05:30; End Time - 24-09-2002 15:00), I would like to know how many minutes were used in three intervals: Working Day from 08:00 to 22:00; Working Day from 22:00 to 08:00; Non Working Day. I would like to say that with that example, I had x minutes on the first interval, w minutes on the second interval and z minutes on the third interval. What's the best way to do this?  

One of Oracle's most overlooked strengths is the ability to do date arithmetic without any conversions. For example, if you take two DATE datatypes and subtract them, Oracle will tell you the difference. Then, one can use formatting to display the results in any manner you wish. So let's see how we can use this to help solve your problem.

Let's look at how we determine how many minutes were used in the first interval. We have three scenarios, the time frame starts before the interval starts, the time frame starts after the interval is over, or the time frame starts in the middle of the interval. Assume time_start is a variable which holds the start time, time_end is a variable which holds the end time, inteval_start is the start of the interval, and interval_end is a variable which holds the end of the interval. The variable total_minutes will hold the total number of minutes spent in the interval.

If time_start < interval_start then
   -- time started before the interval begin
   total_minutes = time_end - interval_start;
   -- check for a negative value. if negative,
   -- set to zero
   if total_minutes < 0 then total_minutes=0;
else if time_start > interval_end then
   -- time started after interval ended
   -- so no time spent in this interval
   total_minutes = 0;
else 
   -- time started in the middle of the interval
   -- two choices now, time ends at the end of the 
   -- interval or time ends before the end of the 
   -- interval
   if time_end < interval_end then
      -- time spent total in this interval
      total_minutes = time_end - time_start;
   else
      -- only take time from start to interval end
      total_minutes = interval_end - time_start;

So we now need to convert this to SQL statements. Our IF-THEN choices will be replaced by DECODE statements. To test for less-than in our DECODE statement, we will use the SIGN function.

SELECT
DECODE(SIGN(time_start-interval_start),-1,DECODE(SIGN(time_end-interval_start),-1,0,time_end-interval_start)),
DECODE(SIGN(interval_end-time_start),-1,0,
DECODE(SIGN(time_end-interval-end),-1,time_end-time_start,interval_end-time_start)))
FROM dual;

Hopefully, you can see how I was able to use DECODEs to make IF-THEN decisions and the SIGN function to test for inequalities. The number returned will be a floating point number. The integer portion is the number of days. The fractional portion is the fraction of a day. So to get this converted to minutes, just multiply this number by 1440 (24 hours * 60 minutes).

This can get quite messy, I agree. So you may wish to code this as a PL/SQL function and have the function return the number of minutes.

 

For More Information


 

This was first published in September 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close