Re: self join for history analyzis

On Jan 26, 2013, at 13:32, Rafał Pietrak <rafal@zorro.isa-geek.com> wrote:

> I have a usage recording table: CREATE TABLE readings(tm timestamp, bytesin int, bytesout int);
>
> The readouts are made "occasionally" - the timespan between the readouts are not very precise, but there is a lot of
thosereadouts. 
>
> when presenting data, for every readout I need to compute the difference between it and the one immediately preceding
it,and divide that by respective measurement interval. 

You don't need a self-join, you need a window function.
See: http://www.postgresql.org/docs/9.1/static/tutorial-window.html

For example:
SELECT timestamp, lag(timestamp) OVER (ORDER BY timestamp) FROM readings;