Thread: help with a query

Pedro Doria Meunier <pdoria@netmadeira.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°

If you do not want to amend your table with extra information, this is
how you do it:

Suppose you have a table

create table events (
 time timestamp,
 object int refers objects(id), -- The thing that had its ignition
turned on or off at this time
 ignition boolean,
 comment varchar
);

You can then do

select distinct on (e1.time, e1.object, e1.comment)
 e1.time as start,
 e2.time as end,
 e2.time - e1.time as duration,
 e1.object,
 e1.comment
from
 events as e1,
 events as e2
where
 e1.object = e2.object
 and e1.time < e2.time
order by
 e1.time, e1.object, e1.comment, e2.time asc;

The trick here is to sort by e2.time in ascending order, and to not
include e2.time (or any of e2:s  fields) in the distinct condition.
This causes all rows where the values from e1 are the same to be
considered duplicates, and only the first one of them included in the
result - which is the one with the lowest e2.time value, since we did
sort on that key in ascending order.

Note that this trick works since order by is processed before the
distinct clause. This is specifically noted in the PostgreSQL manual.

Note that there is a slightly more "intuitive" way of doing this using
a subquery with a min() aggregate function, but this is considerably
less efficient, especially with larger tables (use "explain" and you'll
understund why).