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

2011/10/5 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>

> hello,
>
> let's say that each rows in a table contains a start time and a end
> time ("timeinterval" type),
>

there is no such type ( no result for select * from pg_type where typname ~
'timeinterval' ).
can you show exact table structure (output of psql "\d" or better, CREATE
TABLE command)?

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 ? I guess this could be done with window function and
> lag()
> function but I don't know exactly how. Any suggestion ?
>
>

-- assuming that you actually want lag compared to previous starttime - try
this:
select index, starttime, endtime, starttime - lag(endtime) over(order by
starttime asc) as delta from test;

PS. this question should probably go to "pgslq-sql mailing list more than
"pgsql-general". also please give more details next time. Thanks.