Обсуждение: how do i get differences between rows
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
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.
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.