Collapsing overlapping dates

overlaps.sql

-- base table has id, start_date, end_date

select * from (
  with
  recursive timeline (id, start_date, end_date) as (
    select
      id,
      start_date,
      end_date 
    from base_table

    union

    select
      timeline.id,
      least(timeline.start_date, bt.start_date) start_date,
      greatest(timeline.end_date, bt.end_date) end_date
    from timeline
    join base_table bt
    on timeline.id = bt.id
      and (bt.start_date
            between timeline.start_date and timeline.end_date)
  )

  SELECT distinct
    id,
    min(start_date) over (partition by end_date) start,
    max(end_date) over (partition by start_date) end
  FROM timeline
  ORDER BY id, start, stop
) as cte
;

two_timelines.sql

-- if you need to compare dates of two timelines
-- works if you need something other than OVERLAPS()
-- assumes both tableA and tableB have no start > end

SELECT
  *
FROM tableA
LEFT JOIN tableB
  ON (tableA.start <= tableB.start
    AND tableA.end >= tableB.start)
  OR (tableA.start >= tableB.start
    and tableA.end <= tableB.end)