From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: question with times and intervals
Date: Thu, 26 Jan 2006 12:58:16 +0100
Message-ID: <20060126115816.GA9924@webserv.wug-glas.de> (raw)
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
id=1, status=2, date=2006-01-21:
from 22:00:00 - 23:59:59
===> 2 hours
I need also this for a week or month. How can i calculate this?
Thanks very much for help.
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
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: andreas.kretschmer@schollglas.com
Subject: Re: question with times and intervals
In-Reply-To: <20060126115816.GA9924@webserv.wug-glas.de>
* 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