From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | hjenkins <hjenkins(at)uvic(dot)ca> |
Cc: | Postgresql General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: comparing rows |
Date: | 2007-12-10 22:00:24 |
Message-ID: | 1197324024.7855.77.camel@snafu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 2007-12-10 at 13:31 -0800, hjenkins wrote:
> I would like to take a timeseries of data and extract the rows of data
> flanking the gaps in it. So I need to compare timestamps from two
> adjacent
> rows, and determine if the interval is greater than the standard
> sampling
> interval.
It often helps for us to have a snippet of a table definition to frame
replies. I'll assume that you have a "data" table with a timestamp
column called "ts". I suspect you could use a subquery, like this:
=> select D1.ts as ts1,(select ts from data D2 where D2.ts>D1.ts limit
1) as ts2 from data D1;
I'm uncertain about the performance of this subquery in modern PGs. If
this query works for you, then you can wrap the whole thing in a view or
another subquery in order to compute ts2-ts1, like this:
=> select ts1,ts2,ts2-ts1 as delta from ( <above query> ) X;
This will get you only the timestamps of adjacent rows with large
deltas. The easiest way to get the associated data is to join on the
original data table where ts1=ts or ts2=ts.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-12-10 22:02:19 | Re: slony question |
Previous Message | Obe, Regina | 2007-12-10 21:52:14 | Re: Script to reset all sequence values in the a given DB? |