Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2011/10/6  <depstein@xxxxxxxxxxxxxxxxx>:
>> -----Original Message-----
>> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
>> owner@xxxxxxxxxxxxxx] On Behalf Of thomas veymont
>> Sent: Wednesday, October 05, 2011 5:35 PM
>> To: pgsql-general@xxxxxxxxxxxxxx
>> Subject:  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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux