Split overlapping intervals into smaller, touching ones in PostgreSQL

user_4930441 0
Viewed 90

I need a help with a query that should split some overlapping date intervals in the following way.

For example if we have a following table

----------------------------------------------------------------------
|   column_name    |    value    |    start_date    |    end_date    |
----------------------------------------------------------------------
|     column1      |    value1   |    03-09-2020    |   26-09-2020   |
----------------------------------------------------------------------
|     column1      |    value2   |    07-09-2020    |   20-09-2020   |
----------------------------------------------------------------------

I have a problem coming up with a query to make the following output:

----------------------------------------------------------------------
|   column_name    |    value    |    start_date    |    end_date    |
----------------------------------------------------------------------
|     column1      |    value1   |    03-09-2020    |   07-09-2020   |
----------------------------------------------------------------------
|     column1      |    value2   |    07-09-2020    |   20-09-2020   |
----------------------------------------------------------------------
|     column1      |    value1   |    20-09-2020    |   26-09-2020   |
----------------------------------------------------------------------
postgresql sql
1 Answers

One method is to get all the dates and use a lateral join to figure out the value on that date:

with dates as (
      select start_date as date
      from t
      union 
      select end_date
      from t
     )
select tt.value, d.date as start_date, lead(d.date) over (order by d.date) as end_date
from dates d left join lateral
     (select t.*
      from t
      where start_date <= d.date and
            end_date > d.date
      order by start_date desc  -- this gets the most recent overlapping value
      limit 1
     ) tt
     on true
order by d.date;

Here is a db<>fiddle.

Note this adds an extra row with the final "no value" period. You can easily filter that out if you don't want it.

user_11440350
Related