Message-ID: <43D8BBE7.4050108@archonet.com>
Date: Thu, 26 Jan 2006 12:09:11 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	pgsql-sql@postgresql.org
Subject: Re: question with times and intervals
References: <20060126115816.GA9924@webserv.wug-glas.de>
In-Reply-To: <20060126115816.GA9924@webserv.wug-glas.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

A. Kretschmer wrote:
> Hi,
> 
> I have a table like this:
> 
> test=# select * from status_log ;
>  id | status |        t_start         |         t_end
> ----+--------+------------------------+------------------------
>   1 |      1 | 2006-01-20 23:00:00+01 | 2006-01-21 06:00:00+01
>   1 |      1 | 2006-01-21 06:00:00+01 | 2006-01-21 22:00:00+01
>   1 |      2 | 2006-01-21 22:00:00+01 | 2006-01-22 05:00:00+01
>   1 |      1 | 2006-01-22 05:00:00+01 | 2006-01-22 15:00:00+01
>   1 |      2 | 2006-01-22 15:00:00+01 | 2006-01-23 02:00:00+01
> (5 rows)
> 
> Now i need for a particular intervall (one day or two days, entires
> days) the accumulated time for id=X and status=Y. 
> 
> Exampel:
> 
> id=1, status=1, date=2006-01-21:
> 
> from 00:00:00 - 06:00:00 and
>      06:00:00 - 22:00:00
> 
> ===> 6 hours + 16 hours = 22 hours

OK - all untested...

First step - don't ask for a date, ask between two timestamptz's (which 
I'll call t1, t2)

Then, define two functions: earlier(timestamptz, timesatmptz) and 
later(...) as SQL functions using SELECT ... CASE

SELECT
   later(T1, t_start) AS lower_time
   earlier(T2, t_end) AS upper_time
FROM
   status_log
WHERE
   id = X
   AND status = Y
   AND ts_end >= T1
   AND ts_start <= T2
;

Now (upper_time - lower_time) is the interval you want and summing them 
will give you your answer.

Any help?
-- 
   Richard Huxton
   Archonet Ltd