Re: help with a query

Pedro Doria Meunier <pdoria(at)netmadeira(dot)com> schrieb:

>
> Hi all!
>
> This is most certainly a lame question but perhaps someone is gracious enough
> to lend me a hand& ;-)
>
> I have the following setup in a table:
>
> The first record which is to be found (ok easy enough :D) with a timestamp
> meets a certain condition (ignition off)
> The following record is for the event of ignition on again with a timestamp.
>
> So the question here is: how can I compute the time difference between these
> two records in a single query?
> Better yet: finding all the records that meet the first condition (ignition
> off) and the immediately following records as to compute the time difference.
> ;-)

Okay, let me try.

First, i created a similar table:

test=# select * from test;
id | nr | ts | event
----+----+----------------------------+-------
1 | 1 | 2006-11-05 11:20:34.308945 | 0
2 | 2 | 2006-11-05 11:20:41.245691 | 0
3 | 2 | 2006-11-05 11:20:43.630381 | 1
4 | 1 | 2006-11-05 11:20:49.762882 | 1
5 | 3 | 2006-11-05 11:20:55.427288 | 0
(5 rows)

As we can see, i have a column nr to identify paired rows. The
event-column is similar to your ignition (off-on -> 0-1).
The rows with id 1 and 4, and 2 and 3 paired.

There are only 0-events and paired 1-events or only a 0-event,
and only one pair for every nr.

Now i want to know the elapsed time for every nr (1 and 2) between the
0 and 1 - event:

test=# select a.id,
a.nr,
a.ts as event_off,
a.event,
b.id,
b.ts as event_on,
b.ts-a.ts as elapsed
from test a, test b
where (a.nr=b.nr and a.ts<b.ts);
id | nr | event_off | event | id | event_on | elapsed
----+----+----------------------------+-------+----+----------------------------+-----------------
1 | 1 | 2006-11-05 11:20:34.308945 | 0 | 4 | 2006-11-05 11:20:49.762882 | 00:00:15.453937
2 | 2 | 2006-11-05 11:20:41.245691 | 0 | 3 | 2006-11-05 11:20:43.630381 | 00:00:02.38469
(2 rows)

Hope that helps, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°