2011/10/6 <depst...@alliedtesting.com>: >> -----Original Message----- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of thomas veymont >> Sent: Wednesday, October 05, 2011 5:35 PM >> To: pgsql-general@postgresql.org >> Subject: [GENERAL] gaps/overlaps in a time table : current and previous row >> question >> >> 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 ? > > You can't. The order in which rows are retrieved from a table is undefined, > unless you specify it in your query. If the index cannot be used to specify > the order, then there is no way for you to retrieve rows in the "correct" > order. > > If you could get the rows in the correct order, you could use the lag() > window function to do what you want. >
yes. there was an answer yesterday about doing this with a window function: http://archives.postgresql.org/pgsql-general/2011-10/msg00157.php thanks tom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
