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

[Filip Rembiałkowski <plk.zuber@xxxxxxxxx>]

2011/10/5 thomas veymont <thomas.veymont@xxxxxxxxx>

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.