Re: gaps/overlaps in a time table : current and previous row question

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of thomas veymont
> Sent: Wednesday, October 05, 2011 5:35 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] gaps/overlaps in a time table : current and previous row
> question
>
> hello,
>
> let's say that each rows in a table contains a start time and a end time
> ("timeinterval" type), but the index are not ordered nor consecutive, e.g :
>
> $ select * from T order by starttime
>
> index | starttime | endtime
> ---------+-----------------+-----------------
> 3 | t1 | t2
> 1 | t3 | t4
> 18 | t5 | t6
> 12 | t7 | t8
>
> I want a result that shows time gaps and overlaps in this table, that is :
>
> delta
> ---------+
> t3 - t2 |
> t5 - t4 |
> t7 - t6 |
>
> how would I do that ?

You can't. The order in which rows are retrieved from a table is undefined, unless you specify it in your query. If the index cannot be used to specify the order, then there is no way for you to retrieve rows in the "correct" order.

If you could get the rows in the correct order, you could use the lag() window function to do what you want.