4

Anyone could help me in a query that **merge a interval of datas?
ex: Common select:

SELECT id, date_start, date_end FROM evento ORDER BY date_start

I GOT THIS:
pgsql result

FROM THIS:
web agenda

but a want a query that return this:

01 - 2013-10-11 08:00:00 2013-10-11 15:00:00
02 - 2013-10-11 16:00:00 2013-10-11 19:00:00

Thanks a lot!

Alexandre Hübner
  • 53
  • 1
  • 5
  • 1
    Can you please post your data table (with id, date_start, date_end in the header) in **text** form? – PM 77-1 Oct 08 '13 at 23:34
  • [This SO post](http://stackoverflow.com/questions/11653255/sql-merge-date-ranges) provides answers to a very similar question. 1st answer even has Postgresql SQL Fiddle. – PM 77-1 Oct 08 '13 at 23:40
  • The second line of expected result wouldn't be from `16:00` instead of `14:00`? – MatheusOl Oct 09 '13 at 00:16
  • Ups, yes! I probably was thinking about 4pm! I will fix this, the answer of Kordirko works. Thank you a lot. – Alexandre Hübner Oct 29 '13 at 23:48

1 Answers1

6

You may also try this query (once more solutions beside those given by PM 77-1 in the comment above) :

WITH RECURSIVE cte( id, date_start, date_end ) AS
(
  SELECT id, date_start, date_end
  FROM evento
  UNION 
  SELECT e.id,
         least( c.date_start, e.date_start ),
         greatest( c.date_end, e.date_end )
  FROM cte c
  JOIN evento e
  ON e.date_start between c.date_start and c.date_end
     OR 
     e.date_end between c.date_start and c.date_end
)
SELECT distinct date_start, date_end
FROM (
  SELECT id, 
         min( date_start) date_start, 
         max( date_end ) date_end
  FROM cte
  GROUP BY id
) xx
ORDER BY date_start;

Demo ---> http://www.sqlfiddle.com/#!12/bdf7e/9

however for huge table the performance of this query could be horribly slow, and some procedural approach might perform better.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • Wow! Just now i saw your answer, I didn't receive any warning about this ;~ Thank you a lot man, your query works perfectly! I was a long time trying to do that. – Alexandre Hübner Oct 29 '13 at 23:38
  • How would you write this query for SQL Server 2008? SQL Server does not have the 'least' and 'greatest' functions. – Thracian Jan 30 '14 at 23:47
  • 1
    @Thracian use `CASE` expression, for example `least( x, y )` can be replaced by `case when x < y then x else y end`, similary `greatest( x, y )` can be rewritten into: `case when x > y then x else y end` – krokodilko Jan 31 '14 at 08:49