Find gaps in SQL between datetime Ranges within a startdatetime and enddatetime value

user_18027936 0
Viewed 89

im looking for a SQL Query that can deliver me all of the free time Intervals between a given Range for a Table with two datetime Columns (DATE_FROM, DATE_TILL). As a Requirement: All other entries are not overlapped and the only acceptable distance between each interval is 1 Second. I have found a Solution but this doesnt fill all my Requirements, specially the one where i want to put a given start and end datetime to calculate the missing Intervals if given.

Here is my datatable:

ROW_ID LOCATION_ID   DATE_FROM              DATE_TILL
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

And this is my SQL Query, which is from another Solution in this Plattform where i made some requirements changes.

SELECT DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) AS GAP_FROM, DATE_SUB(DATE_FROM,INTERVAL 1 SECOND) AS GAP_TILL
FROM
    (
        SELECT DISTINCT DATE_FROM, ROW_NUMBER() OVER (ORDER BY DATE_FROM) RN
        FROM overlappingtable T1
        WHERE
            LOCATION_ID = 193 AND
            NOT EXISTS (
                SELECT *
                FROM overlappingtable T2
                WHERE T1.DATE_FROM > T2.DATE_FROM AND T1.DATE_FROM < T2.DATE_TILL
            )
        ) T1
    JOIN (
        SELECT DISTINCT DATE_TILL, ROW_NUMBER() OVER (ORDER BY DATE_TILL) RN
        FROM overlappingtable T1
        WHERE
                LOCATION_ID = 193 AND
            NOT EXISTS (
                SELECT *
                FROM overlappingtable T2
                WHERE T1.DATE_TILL > T2.DATE_FROM AND T1.DATE_TILL < T2.DATE_TILL
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
   DATE_ADD(DATE_TILL,INTERVAL 1 SECOND) < DATE_FROM

This Query delivers me this result:

GAP_FROM              GAP_TILL
2020-01-01 00:00:00   2020-01-31 23:59:59

Which is great, this is the free Interval that i have to deliver between entries that have their ranges and dont overlap. But I want to set in this Query two Parameters for The Main Range for this entries. One for the startdate and the other for enddate. For this example:

startdate = '2019-01-01 00:00:00'
enddate = '9999-12-31 23:59:59'

For LOCATION_ID = 193 i am missing the gap between the startdate('2019-01-01 00:00:00') and the first DATE_FROM for the first entry('2019-02-01 00:00:00').

The result that i would like to deliver should look like this for LOCATION_ID = 193:

GAP_FROM              GAP_TILL
2019-01-01 00:00:00   2019-01-31 23:59:59
2020-01-01 00:00:00   2020-01-31 23:59:59
2023-01-01 00:00:00   9999-12-31 23:59:59

Im really new at SQL and could understand this Query, but i can't develop this further to set these Main Ranges and deliver the missing gaps.

Thanks in Advance

mysql sql
2 Answers

You could join to a sub-query with the start & end datetimes.

Then compare to the previous & next datetimes per location_id.

The previous or next datetimes can be found via the LAG & LEAD functions.

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

Demo on db<>fiddle here

user_40034190

For clarity I would recommend to find the initial gaps, the middle ones, and the ending ones in separate CTEs, as shown below in the b, m, and e CTEs. Then, a simple UNION ALL can combine all of them:

with
p (loc_id, start_date, end_date) as (
  select 193, '2019-01-01 00:00:00', '9999-12-31 23:59:59'
),
r as (
  select location_id, date_from,
    date_add(date_till, interval 1 second) as date_till,
    lead(date_from) over(partition by location_id order by date_from) as next_from
  from overlappingtable t
  cross join p
  where t.location_id = p.loc_id
),
b as (
  select p.start_date as gap_from, r.date_from as gap_till
  from (select * from r order by date_from limit 1) r
  cross join p
  where p.start_date < r.date_from
),
m as (
  select date_till, next_from
  from r
  where date_till < next_from
),
e as (
  select r.date_till, p.end_date
  from (select * from r order by date_till desc limit 1) r
  cross join p
  where r.date_till < p.end_date
)
select * from b
union all select * from m
union all select * from e
order by gap_from

Result:

 gap_from             gap_till            
 -------------------- ------------------- 
 2019-01-01 00:00:00  2019-02-01 00:00:00 
 2020-01-01 00:00:00  2020-02-01 00:00:00 
 2023-01-01 00:00:00  9999-12-31 23:59:59

See running example atDB Fiddle.

The initial CTE p includes the parameters of the query (loc_id, start_date, end_date) and is added for clarity.

user_64361910
Related