Re: [SQL] Clever way to check overlapping time intervals ?
You can convert dates to cube, they have overlap operator && 2011/9/16, Andreas Kretschmer : > Andreas wrote: > >> Hi, >> is there a clever way to check overlapping time intervals ? >> An option named n should be taken from date y to y. >> The same name is ok for another interval. >> >> e.g. table : mytab ( d1 date, d2 date, n text, v text ) >> >> There should be a constraint to provide no row can have a d1 or d2 >> within the interval of another row in case they have the same n. >> >> And no row can have an interval that encloses an existing interval. > > You can use the PERIOD data type: > http://temporal.projects.postgresql.org/reference.html > > and the new (since 9.0) exclusion constraint: > http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ > http://www.google.de/search?hl=de&q=postgresql+exclusion+constraint&aq=0L&aqi=g-L1&aql=&oq=postgresql+exclusion+ > http://thoughts.j-davis.com/2010/03/09/temporal-postgresql-roadmap/ > > > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- pasman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Clever way to check overlapping time intervals ?
On 09/15/11 19:40, Andreas wrote: Hi, is there a clever way to check overlapping time intervals ? An option named n should be taken from date y to y. The same name is ok for another interval. e.g. table : mytab ( d1 date, d2 date, n text, v text ) There should be a constraint to provide no row can have a d1 or d2 within the interval of another row in case they have the same n. And no row can have an interval that encloses an existing interval. self join with "OVERLAPS" operator: select t1.*,t2.* from (select * from mytab) as t1 full join (select * from mytab) as t2 where (t1.d1,t1.d2) overlaps (t2.d1,t2.d2) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Window function sort order help
Dianna Harter wrote: > Having trouble getting a window function to sort correctly. > Given this data > consumer_id | move_date | history_timestamp > -++ >12345| 2008-01-05 | 2007-12-11 06:02:26.842171 >12345| 2008-02-29 | 2008-02-05 07:22:38.04067 >12345| 2008-02-29 | 2008-07-11 09:03:42.44044 >23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <-- >23456| 2009-01-28 | 2008-11-14 01:57:40.264335 >23456| 2009-01-01 | 2008-12-04 17:14:20.27 <-- >23456| 2009-01-01 | 2008-12-31 00:33:37.204968 >23456| 2009-01-01 | 2011-06-08 04:16:41.646521 >34567| 2010-05-07 | 2010-06-08 05:14:43.842172 > I'm trying to get the timestamp when the consumer last changed their > move_date. (Notice consumer_id 23456 set their move_date to 2009-01-01 then > changed and then changed it back. In the end, I want the timestamp from when > they changed it to 2009-01-01 the second time.) > My thought was to do an intermediary step to find the timestamp for each time > it switched. From there I can grab the max(timestamp) for each consumer. > [...] > Any suggestions to get the order by to occur first then the partition by or > maybe there another approach that I could use? If I understand the question correctly, try: | SELECT DISTINCT ON (consumer_id) consumer_id, move_date, history_timestamp |FROM (SELECT consumer_id, move_date, history_timestamp, | LAG(move_date) OVER (PARTITION BY consumer_id ORDER BY consumer_id, history_timestamp) AS previous_move_date | FROM consumer_hist) AS SubQuery |WHERE move_date IS DISTINCT FROM previous_move_date |ORDER BY consumer_id, history_timestamp DESC; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
