pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: teknokrat <teknokrat@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: how do i get differences between rows
Date: Tue, 23 Dec 2003 13:23:35 +0000
Message-ID: <bsc41u$1ps8$1@news.hub.org> (raw)
In-Reply-To: <20031224033156.GB16470@wolff.to>
References: <brs64d$1u47$1@news.hub.org>
	<20031224033156.GB16470@wolff.to>

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.





view thread (3+ messages)  latest in thread

Message-ID: <bsc41u$1ps8$1@news.hub.org>
Permalink:  ../bsc41u$1ps8$1@news.hub.org/
Also on:    postgresql.org/message-id/bsc41u$1ps8$1@news.hub.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: teknokrat@yahoo.com
  Subject: Re: how do i get differences between rows
  In-Reply-To: <bsc41u$1ps8$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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