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

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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