Select date ranges with a 50% overlap to another date range I have a series of date ranges with columns e.g.
start_date, end_date, period_name

   1         100     Roman
 -50          75     Iron Age b
-100          20     Iron Age a


My team have asked me to build 2 queries:


Select the Roman period and any which overlaps
same as 1 but only those ranges which overlap >40%


I'm assuming I would have to write a function in postgresql (PL/pgSQL), any help is very welcome.
This can be expressed using Postgres' range types quite nicely

First query: find all ages that overlap with the Roman age:

select *
from ages a1
where period_name &lt;&gt; 'Roman'
  and exists (select *
              from ages a2
              where period_name = 'Roman' 
                and int4range(a1.start_date, a1.end_date) &amp;&amp; int4range(a2.start_date, a2.end_date))


For the second one it's necessary to calculate the length of the overlap, this can be done using the intersection operator for ranges: *, e.g. int4range(0,100,'[]') * int4range(-50,75,'[]') yields 76. This result has to be compared to the total length of the Roman age to get the percentage. 

As this requires access to both ages (the roman and the overlapping) at the same time, this is better done using a join. 

To make the final query easier to read, I use common table expression to turn the two columns into a proper range, then I use that to make the join. 

Once I have that. the overlapping percentage is easy to calculate:

with ranges as (
   select period_name, 
          int4range(start_date, end_date, '[]') as period,
          upper(int4range(start_date, end_date, '[]')) - lower(int4range(start_date, end_date, '[]')) as years
   from ages
), overlapping as (
  select o.period_name as overlapping_period_name, 
         r.period as roman_period, 
         o.period as overlapping_period, 
         r.period * o.period as overlap_length, 
         r.years as roman_years, 
         o.years as other_yers
  from ranges r
    join ranges o on r.period &amp;&amp; o.period and o.period_name &lt;&gt; 'Roman'
  where r.period_name = 'Roman'
)
select overlapping_period_name, 
       overlapping_period,
       (upper(overlap_length) - lower(overlap_length))::numeric / roman_years as overlap_percent
from overlapping;


The cast ::numeric is needed because otherwise it would be an integer division and the result would be 0 not 0.75

To display only those with a minimum of 40% overlap just add a where condition:

.... 
select *
from overlapping
where (upper(overlap_length) - lower(overlap_length))::numeric / roman_years &gt;= 0.4


Online example: http://rextester.com/JIOAV33074



If you need to do a lot of calculations on the "periods", it might be a good idea to store them as a range directly, that saves a lot of converting back and forth when doing calculations.