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