Re: 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 ? 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

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.

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.
>
>

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of thomas veymont
> Sent: Wednesday, October 05, 2011 5:35 PM
> To: pgsql-general(at)postgresql(dot)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.

2011/10/6 <depstein(at)alliedtesting(dot)com>:
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of thomas veymont
>> Sent: Wednesday, October 05, 2011 5:35 PM
>> To: pgsql-general(at)postgresql(dot)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

On Wed, 2011-10-05 at 15:35 +0200, thomas veymont wrote:
> 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 :

I think your question has already been answered, but I thought you might
be interested in:

Period data type:
http://pgxn.org/dist/temporal/

Or Exclusion Constraints, which can prevent overlapping ranges:
http://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

Also, I'm currently working on a feature called Range Types, which will
hopefully be in 9.2.

Regards,
Jeff Davis