pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow do i get differences between rows
3+ messages / 2 participants
[nested] [flat]
* how do i get differences between rows
@ 2003-12-18 12:21 teknokrat <teknokrat@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: teknokrat @ 2003-12-18 12:21 UTC (permalink / raw)
To: pgsql-sql
I am after an sql query that can give the differnces between consecutive
timestamp values in the resultset.
anyone know how to do this?
thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how do i get differences between rows
@ 2003-12-23 13:23 teknokrat <teknokrat@yahoo.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: teknokrat @ 2003-12-23 13:23 UTC (permalink / raw)
To: pgsql-sql
Bruno Wolff III wrote:
> On Thu, Dec 18, 2003 at 12:21:35 +0000,
> teknokrat <teknokrat@yahoo.com> wrote:
>
>>I am after an sql query that can give the differnces between consecutive
>>timestamp values in the resultset.
>>anyone know how to do this?
>
>
> I think you can do something like the following (untested) on most systems.
> select a.stamp - (select max(stamp) from tablename
> where tablename.stamp < a.stamp) from tablename;
>
> For postgres this (also untested) might be faster if there is an index
> on stamp.
> select a.stamp - (select stamp from tablename where tablename.stamp < a.stamp
> order by stamp desc limit 1) from tablename;
>
> The above relies on timestamps being unique. The difference for the smallest
> timestamp will be null.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
this may give me difference between current row and oldest row but what
I want is row(i).timestamp - row(i-1).timestamp. Also I would like to do
it for timestamp values whose hours are within normal working hours i.e 9-5.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: how do i get differences between rows
@ 2003-12-24 03:31 Bruno Wolff III <bruno@wolff.to>
parent: teknokrat <teknokrat@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2003-12-24 03:31 UTC (permalink / raw)
To: teknokrat <teknokrat@yahoo.com>; +Cc: pgsql-sql
On Thu, Dec 18, 2003 at 12:21:35 +0000,
teknokrat <teknokrat@yahoo.com> wrote:
> I am after an sql query that can give the differnces between consecutive
> timestamp values in the resultset.
> anyone know how to do this?
I think you can do something like the following (untested) on most systems.
select a.stamp - (select max(stamp) from tablename
where tablename.stamp < a.stamp) from tablename;
For postgres this (also untested) might be faster if there is an index
on stamp.
select a.stamp - (select stamp from tablename where tablename.stamp < a.stamp
order by stamp desc limit 1) from tablename;
The above relies on timestamps being unique. The difference for the smallest
timestamp will be null.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-24 03:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-18 12:21 how do i get differences between rows teknokrat <teknokrat@yahoo.com>
2003-12-24 03:31 ` Bruno Wolff III <bruno@wolff.to>
2003-12-23 13:23 ` teknokrat <teknokrat@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox