Postgres Merging Overlapping time stamp records

Question

i am having the below set of data in a table named xy

 siteid   ;    eventtime        ;     ceasetime
'LCAI2995';'2017-07-17 08:24:00';'2017-07-17 08:50:00'
'LCAI2995';'2017-07-17 08:22:00';'2017-07-17 08:50:00'
'LCAI2995';'2017-07-16 18:40:00';'2017-07-16 18:42:00'
'LCAI2995';'2017-07-16 18:39:00';'2017-07-16 18:48:00'
'LCAI2995';'2017-07-16 18:31:00';'2017-07-16 18:33:00'
'LCAI2995';'2017-07-16 18:30:00';'2017-07-16 18:38:00'
'LCAI2995';'2017-07-16 18:09:00';'2017-07-16 18:11:00'
'LCAI2995';'2017-07-16 18:08:00';'2017-07-16 18:11:00'
'LCAI2995';'2017-07-16 17:30:00';'2017-07-16 17:44:00'
'LCAI2995';'2017-07-16 17:28:00';'2017-07-16 17:45:00'
'LCAI2995';'2017-07-16 16:03:00';'2017-07-16 16:23:00'
'LCAI2995';'2017-07-16 16:01:00';'2017-07-16 16:23:00'
'LCAI2995';'2017-07-16 12:11:00';'2017-07-16 13:00:00'
'LCAI2995';'2017-07-16 12:10:00';'2017-07-16 13:01:00'
'LCAI2995';'2017-07-16 09:12:00';'2017-07-16 09:51:00'
'LCAI2995';'2017-07-16 09:11:00';'2017-07-16 09:50:00'
**'LCAI2995';'2017-07-16 08:42:00';'2017-07-16 09:04:00'**
**'LCAI2995';'2017-07-16 08:25:00';'2017-07-16 09:03:00'**
**'LCAI2995';'2017-07-16 08:23:00';'2017-07-16 08:41:00'**
'LCAI2995';'2017-07-16 07:39:00';'2017-07-16 08:21:00'
'LCAI2995';'2017-07-16 07:37:00';'2017-07-16 08:21:00'

which is overlapping in event and cease times more than once (overlapped records are highlighted) so i used the below query to merge the overlapped records in one record

select * from(
with set1 as (select * from xy where cellid is null
            )

            select distinct a.siteid, case when a.eventtime < set1.eventtime then a.eventtime else set1.eventtime end , case when a.ceasetime> set1.ceasetime then a.ceasetime else set1.ceasetime end  from
            (select * from xy so where cellid is null
            )a , set1 

             where a.siteid =set1.siteid and not a.alarmid = set1.alarmid
            and ( (a.eventtime,a.ceasetime) overlaps (set1.eventtime, set1.ceasetime) or a.ceasetime - set1.eventtime ='00:00:00'::interval))x
            ORDER by x.siteid,x.eventtime asc

it's output is the below

siteid    ;    eventtime        ;     ceasetime
'LCAI2995';'2017-07-16 07:37:00';'2017-07-16 08:21:00'
**'LCAI2995';'2017-07-16 08:23:00';'2017-07-16 09:03:00'**
**'LCAI2995';'2017-07-16 08:25:00';'2017-07-16 09:04:00'**
'LCAI2995';'2017-07-16 09:11:00';'2017-07-16 09:51:00'
'LCAI2995';'2017-07-16 12:10:00';'2017-07-16 13:01:00'
'LCAI2995';'2017-07-16 16:01:00';'2017-07-16 16:23:00'
'LCAI2995';'2017-07-16 17:28:00';'2017-07-16 17:45:00'
'LCAI2995';'2017-07-16 18:08:00';'2017-07-16 18:11:00'
'LCAI2995';'2017-07-16 18:30:00';'2017-07-16 18:38:00'
'LCAI2995';'2017-07-16 18:39:00';'2017-07-16 18:48:00'
'LCAI2995';'2017-07-17 08:22:00';'2017-07-17 08:50:00'

the problem is that it merged the 4 overlapped records (highlighted in the first table) and produced another 2 overlapped records (highlighted in the above table)!

the question is how can i run the above query recursively so that it keep merging records till there is no overlapping ? knowing that table xy contains a lot of siteids (the above is only an example).

Problem Solved, Thanks to the brilliant [answer](https://stackoverflow.com/a/26330000/6249272) of Gordon Linoff, — Helmy, Jul 19 '17 at 09:55
Possible duplicate of [Merge continuous rows with Postgresql](https://stackoverflow.com/questions/26329513/merge-continuous-rows-with-postgresql) — krokodilko, Jul 19 '17 at 17:50

Postgres Merging Overlapping time stamp records

0 Answers0