best way to calculate accumulating intervals timestamps

From: Kenji Morishige <kenjim(at)juniper(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: kenjim(at)juniper(dot)net, test-tools(at)juniper(dot)net, Joel Holveck <joelh(at)juniper(dot)net>
Subject: best way to calculate accumulating intervals timestamps
Date: 2007-04-02 20:31:02
Message-ID: 20070402203102.GL23586@juniper.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kev 2007-04-02 20:40:10 pg_live
Previous Message Eric B. Ridge 2007-04-02 20:16:47 Re: VACUUM FULL takes long time to complete