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

I think you need to get the full list of change dates first. Assuming
you're searching over a time period between "period_from" and
"period_to":

SELECT change_time, sum(diff) as total_diff FROM (
SELECT starttime as change_time, 1 AS diff FROM t WHERE starttime >
period_from AND endtime < period_to
UNION ALL
SELECT endtime as change_time, -1 AS diff FROM t WHERE endtime >
period_from AND endtime < period_to
) a
GROUP BY change_time
HAVING sum(diff) <> 0
ORDER BY change_time asc

I used this in a pgplsql function to produce a very simular result to
what you were looking for. You need to start by finding how many time
periods overlapped period_from, then accumulatively add on
"total_diff" for each row you process.

Hope this helps.

2011/10/5 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>:
>
>
> 2011/10/5 thomas veymont <thomas(dot)veymont(at)gmail(dot)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.
>
>