Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
-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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
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
[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 ? 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gaps/overlaps in a time table : current and previous row question
2011/10/5 thomas veymont thomas.veym...@gmail.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.
Re: [GENERAL] 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.zu...@gmail.com: 2011/10/5 thomas veymont thomas.veym...@gmail.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
