I'm hoping there is some way to do this without writing a function, but there
may be no way around it.
I have a table that contains two epoch based time stamps, 'start' and
'finish'. They are both timestamp values that are data wherehoused for
resource utilization monitoring. Management wants to know what percentage of
utilization of a particular resource during the regular work ours. 8AM-6PM.
So I need to somehow accumulate all the periods of time between start and
finish that fall between 8AM and 6PM in the most efficient way. The added
complexity occurs when the overall time restraint concerned spans over
multiple days or arbitrary time segments as we need to make sure to account
for any start and finish time periods that may not be fully within the scope
of the input range.
A Random Day:
5AM----------8AM---------------------------------------6PM-----------------11PM
|--------------------------| |----------------------|
start finish start finish
|---------------------| |-----------|
Period A Period B
Another Random Day with checkouts that overun the range:
5AM----------8AM---------------------------------------6PM-----------------11PM
-------------|----A----| <- a checkout period that has spanned multiple days
finish start
|----------B-----------|-----------------------
^ a checkout period that goes beyond range
Management wants to know A+B over the course of multiple days and the start
and finish times can occur in arbitrary times. Any ideas for quickest way to
solve this problem? I know I can do it the dirty way in perl or whatever,
but I was hoping for a solution in SQL/PLSQL.
Kenji