Search Postgresql Archives

Re: help with a query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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°


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux