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