Re: obtain the difference between successive rows

On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> On 20/10/2012 11:54, ochaussavoine wrote:
>> Hi,
>> I have a table 'tmvt' with a field 'created' in the row, and would like to
>> compute the difference between successive rows. The solution I found is:
>>
>
> I think you can do it with a window function.
>
> http://www.postgresql.org/docs/9.2/static/tutorial-window.html
> http://www.postgresql.org/docs/9.2/static/functions-window.html
>
> Ray.

In particular you're looking probably for the lag() window function.
For example if you have a timestamp column "ts" that's increasing
monotonically and you want to check the difference of each row's
timestamp with the chronologically previous row's timestamp you'd do
something like:
$ SELECT id, ts, lag(ts) OVER (order by ts) AS prev_ts FROM mytable;
This will display as third column the previous row's ts.

You may find reading this introduction to window fuctions useful:
https://www.pgcon.org/2009/schedule/attachments/98_Windowing%20Functions.pdf

best tregards,
Thalis