Find Gaps In Date Ranges

A couple of months ago, I have posted an article about finding unused key ranges. Today, I faced a very similar problem: find gaps in time ranges.

I have adapted the sql of the previous post to work with date ranges. To avoid problems with the maximum date 9999-12-31, I do convert the dates to a number (the number of the day in the Julian calendar).

Here is what I came up with:

-- drop test table
drop table timeline;

-- create test table
create table timeline (id number, valid_from date, valid_until date);

-- populate test table 
-- - leave two gaps in the time dimension
insert into timeline values 
(1, to_date('2004-01-01', 'YYYY-MM-DD'), to_date('2004-07-30', 'YYYY-MM-DD'));
--insert into timeline values 
--(2, 0, to_date('2004-07-31', 'YYYY-MM-DD'), to_date('2004-07-31', 'YYYY-MM-DD'));
insert into timeline values 
(3, to_date('2004-08-01', 'YYYY-MM-DD'), to_date('2005-10-31', 'YYYY-MM-DD'));
--insert into timeline values 
--(4, 0, to_date('2005-11-01', 'YYYY-MM-DD'), to_date('2006-12-31', 'YYYY-MM-DD'));
insert into timeline values 
(5, to_date('2007-01-01', 'YYYY-MM-DD'), to_date('9999-12-31', 'YYYY-MM-DD'));

-- select the gaps
select to_date(decode(prev_end_dt
                     ,0 ,1721424  -- 0001-01-01 (the minimum date)
                        ,prev_end_dt+1
                     )
              ,'J')                  gap_start_dt
,      to_date(next_start_dt-1,'J')  gap_end_dt
from   ( 
   select lag(to_number(to_char(valid_until,'J')), 1, 0) 
             over (order by valid_until)     prev_end_dt
   ,      to_number(to_char(valid_from,'J')) next_start_dt
   from   timeline
   union all
   select nvl(max(to_number(to_char(valid_until,'J'))),0)
   ,      5373484 -- 9999-12-31 (the maximum date)
   from   timeline
       )
where  prev_end_dt+1 < next_start_dt
/