Extracting name values from records having overlapping dates

Question

I have a table containing areas whose size changes over time. The timeframe (in years) for which an area size is valid is stored in the columns "VALID_FROM" and "VALID_UNTIL". The timeframes for one area must not overlap. I want to create a query with which I can check if someone has entered overlapping timeframes.

In the example table the validity timeframes from area1 do overlap.

Table "areas":

NAME	SIZE	VALID_FROM	VALID_UNTIL
area1	55	1990	2005
area1	40	2000	2009
area1	45	2010	2099
area2	79	1990	2099
area3	33	1990	1999
area3	37	2000	2009

Note that end dates are inclusive e.g. there is no gap in the two dates for area3.

It would be sufficient to have a result like this:

NAME	REMARK
area1	'timeframes overlap!'

My problem is that an area may occure n times in the table, I dont know how to compare the "VALID_FROM" and "VALID_UNTIL" columns if there could be an arbitrary number of rows for one area.

So far I got as far as reducing the table to those rows which occur more than once.

WITH duplicats AS (
    SELECT `NAME`
    FROM `areas`
    GROUP BY `NAME`
    HAVING COUNT(`NAME`) > 1
)
SELECT * 
FROM       `areas`
INNER JOIN `duplicats`
        ON `areas`.`NAME` = `duplicats`.`NAME`

Answer 1

In MySQL 8.x, you could subtract the previous valid_until value from the valid_from value, if there is a negative difference then there is an overlap.

select NAME, 'timeframes overlap!' AS Remark
from
(
  select *,
    VALID_FROM - lag(VALID_UNTIL) over (partition by NAME order by VALID_FROM) df
  from areas
) t
group by NAME
having sum(df <= 0) > 0

demo

Answer 2

You can do it by self join your tables :

select s1.*
from mytable s1
inner join mytable s2 on (
    s1.VALID_FROM between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s1.VALID_UNTIL between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s2.VALID_FROM between s1.VALID_FROM and s1.VALID_UNTIL
) and s1.NAME = s2.NAME and s1.VALID_FROM <> s2.VALID_FROM and s1.VALID_UNTIL <> s2.VALID_UNTIL

Result :

NAME    SIZE    VALID_FROM  VALID_UNTIL
area1   40      2000        2009
area1   55      1990        2005

Or if you want result grouped by area then :

select s1.NAME, group_concat(DISTINCT concat(s1.VALID_FROM,'-', s1.VALID_UNTIL)) as REMARK
from mytable s1
inner join mytable s2 on (
    s1.VALID_FROM between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s1.VALID_UNTIL between s2.VALID_FROM and s2.VALID_UNTIL
    OR
    s2.VALID_FROM between s1.VALID_FROM and s1.VALID_UNTIL
) and s1.NAME = s2.NAME and s1.VALID_FROM <> s2.VALID_FROM and s1.VALID_UNTIL <> s2.VALID_UNTIL
group by s1.NAME

Result :

NAME    REMARK
area1   1990-2005,2000-2009

Demo here

Answer 3

To test for overlaps you would simply:

select *
from t
where exists (
    select *
    from t as x
    where x.id <> t.id -- pri. key check is needed to avoid comparing row with itself
    and x.name = t.name
    and t.valid_until >= x.valid_from
    and t.valid_from  <= x.valid_until
)

The two from-until date comparisons are sufficient to find all kind of overlaps. The answer assumes until date is inclusive.

Answer 4

JOIN a calendar table or cte. Do a GROUP BY and use HAVING to find years having two (or more) rows with the same area.

with recursive years (y) as
    (select min(valid_from) from areas
     union all
     select y + 1 from years
     where y <= (select max(valid_until) from areas)
    )
select distinct a.name
from areas a
join years on y between valid_from and valid_until
group by a.name, y
having count(*) > 1;