postgresql: splitting time-periods at overlaps

Question

I have a table with overlapping time periods for a number of units. For each unit, I would like to split the time period at the beginning and end of each temporal overlap.

Example with country-periods:

cntry     |  startdate    |   enddate    |

A         |  1960-01-01   | 1989-12-31   |

B         |  1955-01-01   | 1974-12-31   |
B         |  1975-01-01   | 1999-12-31   |

Desired output:

cntry     |  startdate    |   enddate    |

A         |  1960-01-01   | 1974-12-31   |
A         |  1975-01-01   | 1989-12-31   |

B         |  1955-01-01   | 1959-12-31   |
B         |  1960-01-01   | 1974-12-31   |
B         |  1975-01-01   | 1999-12-31   |

Also see this illustration here for clarification

This is closely related to a question I asked earlier, but could not be resolved with the solution used there. Any comments or suggestions on what would be the best approach in this case would be very welcome!

score 1 · Accepted Answer · answered Aug 02 '17 at 20:42

A recursive CTE will let you break up the intervals, and then break up those intervals even further. Here's an example that will work with the given data. It's a bit of a hack, so you might want to refine it.

with cuts as (
    select startdate as cut_date from country
    ),
cntry_cuts as (
    select * from country where 1=0
    union
    select * from (
        select cntry, startdate, cast(cuts.cut_date - interval '1 day' as date) as enddate
        from 
            country as c
            cross join cuts
            where cuts.cut_date > startdate and cuts.cut_date < enddate
        union
        select cntry, cuts.cut_date as startdate, enddate
        from country as c 
        cross join cuts
            where cuts.cut_date > startdate and cuts.cut_date < enddate
        union
        select cntry, startdate, enddate
        from country as c cross join cuts
        where (select max(cut_date) from cuts) < enddate
        ) as x
    )
select cntry, startdate, min(enddate)
from cntry_cuts
group by cntry, startdate
order by cntry, startdate;

Note that the first, non-recursive, part of the recursive CTE is used only to establish the output format; none of the original data are added to the output format, hence the WHERE 1=0 condition.

postgresql: splitting time-periods at overlaps

1 Answers1