Find Gaps In Time Ranges II

A couple of months ago, I have posted an article about finding gaps in date ranges. The SQL worked for a date precision of days. Today, I had to find gaps in time ranges (with a precision of seconds).

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 three gaps in the time dimension
insert into timeline values 
(1, to_date('2004-01-01', 'YYYY-MM-DD'), to_date('2004-07-30 12:00:05', 'YYYY-MM-DD HH24:MI:SS'));
--insert into timeline values 
--(2, to_date('2004-07-30 12:00:06', 'YYYY-MM-DD HH24:MI:SS'), to_date('2004-07-30 12:00:07', 'YYYY-MM-DD HH24:MI:SS'));
insert into timeline values 
(2, to_date('2004-07-30 12:00:08', 'YYYY-MM-DD HH24:MI:SS'), to_date('2004-07-31 18:10:10', 'YYYY-MM-DD HH24:MI:SS'));
--insert into timeline values 
--(3, 0, to_date('2004-07-31 18:10:11', 'YYYY-MM-DD HH24:MI:SS'), to_date('2004-07-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));
insert into timeline values 
(4, to_date('2004-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), to_date('2005-10-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));
--insert into timeline values 
--(5, 0, to_date('2005-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), to_date('2006-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));
insert into timeline values 
(6, to_date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), to_date('9999-12-31 18:27:13', 'YYYY-MM-DD HH24:MI:SS'));

-- select the gaps
select prev_end_dt   + 1/24/60/60  gap_start_dt
,      next_start_dt - 1/24/60/60  gap_end_dt
from   ( 
   select lag(valid_until, 1, to_date('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) 
             over (order by valid_until)     prev_end_dt
   ,      valid_from next_start_dt
   from   timeline
   union all
   select nvl(max(valid_until), to_date('0001-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
   ,      to_date('9999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS') 
   from   timeline
       )
where  prev_end_dt + 1/24/60/60 < next_start_dt
;