Thread: best way to calculate accumulating intervals timestamps

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

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

Without using a function, you will need an auxillary table that holds calendar dates to join
against.  However, you can simulate the auxilary table by using the generate_series function.
Also, I expect that the UNIONS can be eliminated if you use the CASE predicate to handle the
various conditions.


SELECT
        A.resource,
        B.calendardate,
        A.endtime - A.starttime AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on
(
   (A.calendardate + '8 hours') <= A.starttime
AND
   (A.calendardate + '16 hours') >= A.enddate
)

UNION

SELECT
        A.resource,
        B.calendardate,
        A.endtime - (A.calendardate + '8 hours') AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on
(
   A.starttime < (A.calendardate + '8 hours')
AND
   (A.calendardate + '16 hours') >= A.enddate
)

UNION

SELECT
        A.resource,
        B.calendardate,
        (A.calendardate + '16 hours') - A.starttime AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on
(
   (A.calendardate + '8 hours') <= A.starttime
AND
    A.enddate > (A.calendardate + '16 hours')
)

UNION

SELECT
        A.resource,
        B.calendardate,
        '10 hours' AS duration
FROM
        your_table A
INNER JOIN
        aux_calendartable B
on
(
     A.starttime < (A.calendardate + '8 hours')
AND
     A.enddate > (A.calendardate + '16 hours')
)
;