Re: gaps/overlaps in a time table : current and previous row question
| От | Filip Rembiałkowski |
|---|---|
| Тема | Re: gaps/overlaps in a time table : current and previous row question |
| Дата | |
| Msg-id | CAP_rwwnGrwmtV28QaNkgaH1k5NV6wa7nHWQ62A6-PK7k7Pk4kg@mail.gmail.com обсуждение исходный текст |
| Ответ на | gaps/overlaps in a time table : current and previous row question (thomas veymont <thomas.veymont@gmail.com>) |
| Ответы |
Re: gaps/overlaps in a time table : current and previous
row question
|
| Список | pgsql-general |
2011/10/5 thomas veymont <thomas.veymont@gmail.com>
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)?
-- 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.
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.
В списке pgsql-general по дате отправления: