MySQL 8.0

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

create table overlappingtable (
 ROW_ID INT, 
 LOCATION_ID INT, 
 DATE_FROM DATETIME, 
 DATE_TILL DATETIME
)
insert into overlappingtable 
(ROW_ID, LOCATION_ID, DATE_FROM, DATE_TILL) values
(1, 193, '2019-02-01 00:00:00', '2019-12-31 23:59:59'), 
(2, 193, '2020-02-01 00:00:00', '2020-12-31 23:59:59'), 
(3, 193, '2021-01-01 00:00:00', '2021-12-31 23:59:59'), 
(4, 193, '2022-01-01 00:00:00', '2022-12-31 23:59:59'), 
(5, 204, '2020-01-01 00:00:00', '2021-12-31 23:59:59');

Records: 5  Duplicates: 0  Warnings: 0


WITH CTE_UNDERLAPS AS 
(
  SELECT t.*
  , LAG(DATE_TILL)  OVER (PARTITION BY LOCATION_ID ORDER BY DATE_FROM, DATE_TILL) AS PREV_DATE_TILL
  , LEAD(DATE_FROM) OVER (PARTITION BY LOCATION_ID ORDER BY DATE_FROM, DATE_TILL) AS NEXT_DATE_FROM
  , l.*
  FROM overlappingtable t
  JOIN (
    SELECT 
      CAST('2019-01-01 00:00:00' AS DATETIME) AS START_DATETIME
    , CAST('9999-12-31 23:59:59' AS DATETIME) AS END_DATETIME
  ) l ON DATE_FROM >= START_DATETIME AND DATE_TILL <= END_DATETIME
) 
SELECT LOCATION_ID
, COALESCE(DATE_ADD(PREV_DATE_TILL,INTERVAL 1 SECOND), START_DATETIME) AS DATE_FROM
, DATE_SUB(DATE_FROM,INTERVAL 1 SECOND) AS DATE_TILL
FROM CTE_UNDERLAPS
WHERE COALESCE(DATE_ADD(PREV_DATE_TILL,INTERVAL 1 SECOND), START_DATETIME) < DATE_FROM
UNION
SELECT LOCATION_ID
, DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) AS DATE_FROM
, COALESCE(DATE_SUB(NEXT_DATE_FROM,INTERVAL 1 SECOND), END_DATETIME) AS DATE_TILL
FROM CTE_UNDERLAPS
WHERE DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) < COALESCE(NEXT_DATE_FROM, END_DATETIME)

ORDER BY LOCATION_ID, DATE_FROM, DATE_TILL


LOCATION_ID
DATE_FROM
DATE_TILL


193
2019-01-01 00:00:00
2019-01-31 23:59:59

193
2020-01-01 00:00:00
2020-01-31 23:59:59

193
2023-01-01 00:00:00
9999-12-31 23:59:59

204
2019-01-01 00:00:00
2019-12-31 23:59:59

204
2022-01-01 00:00:00
9999-12-31 23:59:59

LOCATION_ID	DATE_FROM	DATE_TILL
193	2019-01-01 00:00:00	2019-01-31 23:59:59
193	2020-01-01 00:00:00	2020-01-31 23:59:59
193	2023-01-01 00:00:00	9999-12-31 23:59:59
204	2019-01-01 00:00:00	2019-12-31 23:59:59
204	2022-01-01 00:00:00	9999-12-31 23:59:59