2011/10/5 thomas veymont
<thomas.veymont@gmail.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.