Monday, December 10, 2007

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

No comments: