| From: | thomas veymont <thomas(dot)veymont(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | gaps/overlaps in a time table : current and previous row question |
| Date: | 2011-10-05 13:35:06 |
| Message-ID: | CAHcTkqqHCwFjw0OyojmnzZWtTaNhFgDNc9_UHSb6Mn+8DR0C0A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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 ? I guess this could be done with window function and lag()
function but I don't know exactly how. Any suggestion ?
thanks
I guess my question is more about
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Phil Couling | 2011-10-05 13:54:13 | Re: how to select one column into another in same table? |
| Previous Message | Raymond O'Donnell | 2011-10-05 12:25:28 | Re: Postgresql-8.2 Replication |