MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
  NAME	varchar(20),
  SIZE	int,
  VALID_FROM	int,
  VALID_UNTIL   int
);

insert into mytable values
('area1',	55,	1990,	2005),
('area1',	40,	2000,	2009),
('area1',	45,	2010,	2099),
('area2',	79,	1990,	2099),
('area3',	33,	1990,	1999),
('area3',	37,	2000,	2009);

Records: 6  Duplicates: 0  Warnings: 0


select *
from mytable;


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


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


NAME
SIZE
VALID_FROM
VALID_UNTIL


area1
40
2000
2009

area1
55
1990
2005


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


NAME
REMARK


area1
1990-2005,2000-2009

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