From: Richard Huxton <dev@archonet.com>
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: question with times and intervals
Date: Thu, 26 Jan 2006 12:09:11 +0000
Message-ID: <43D8BBE7.4050108@archonet.com> (raw)
In-Reply-To: <20060126115816.GA9924@webserv.wug-glas.de>
References: <20060126115816.GA9924@webserv.wug-glas.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, andreas.kretschmer@schollglas.com
Subject: Re: question with times and intervals
In-Reply-To: <43D8BBE7.4050108@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox