¿Cómo encontrar intervalos libres en una secuencia temporal?

Ésta fue la pregunta que surgió en el transcurso de un proyecto en el que los usuarios tenían que dar de alta periodos temporales con una duración variable y que no tenían por qué seguir un orden. Estos periodos temporales se registraron en una tabla general de periodos, en la que se asociaba a cada usuario los periodos que iba dando de alta con las fechas de inicio y final de periodo. Cada vez que un usuario iba a registrar un nuevo periodo el sistema debía comprobar si este periodo existía ya o era necesario darlo de alta. Pero, ¿cómo buscar si el nuevo periodo de tiempo estaba ya comprendido entre los registrados anteriormente por el usuario?

La respuesta que finalmente encontramos fue utilizar las funciones analíticas MAX y LEAD. Gracias a estas dos funciones, una simple SELECT nos podía devolver los intervalos libres entre los periodos ya registrados en la tabla.

Para demostrar su funcionamiento, primero vamos a crear un pequeño escenario de pruebas. En la tabla «lead_test» vamos a insertar una serie de registros con fechas de inicio y final que nos permitan probar la solución:

-- Clean environment
drop table lead_test;

-- Create test environment
create table lead_test (id number, date_start date, date_end date);

insert into lead_test values(1, to_date('01/01/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/02/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(3, to_date('01/03/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/04/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(4, to_date('15/03/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/05/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(5, to_date('01/05/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/06/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));

commit;

-- Alter default date format
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Una vez ejecutadas las sentencias que preparan el entorno de pruebas vamos a comprobar cómo queda nuestra tabla «lead_test:

-- Show test data
select * from lead_test;

        ID DATE_START          DATE_END
---------- ------------------- -------------------
         1 01/01/2014 12:00:01 01/02/2014 12:00:00
         3 01/03/2014 12:00:01 01/04/2014 12:00:00
         4 15/03/2014 22:00:00 01/05/2014 12:00:00
         5 01/05/2014 12:00:01 01/06/2014 12:00:00

Estos datos nos proporcionan una casuística variada:

• Hemos omitido el registro con ID = 2 para dejar el intervalo libre.
• Los registros con ID 3 y 4 se solapan en el tiempo.
• Los registros con ID 4 y 5 empiezan y acaban en el mismo momento, justo uno a continuación del otro pero sin solapamiento.

En una primera aproximación, se elaboró la siguiente consulta que nos permitía obtener los intervalos que no se encontraban cubiertos por ninguno de los periodos registrados en la tabla:

-- Search All Gaps
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end;

Como resultado obtendremos el intervalo que existe entre los registros con ID 1 y 3. Pero también el intervalo entre los registros con ID 4 y 5, puesto que no hay solapamiento:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01
01/05/2014 12:00:00 01/05/2014 12:00:01

Con una pequeña modificación en la consulta podemos regular la duración mínima de los intervalos que queremos encontrar. Y, de este modo, podemos descartar aquellos intervalos que resulten demasiado pequeños para nuestros requerimientos:

-- Search Gaps > 1 sec.
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end
      and (gap_end - gap_start) > (1/24/60/60);

Al incluir como condición que el tiempo entre final de un intervalo y el inicio del intervalo que le sigue sea mayor a 1 segundo (la fórmula 1/24/60/60 representa un segundo en operaciones con fechas en las consultas SQL de Oracle) obtendremos solo el primer intervalo, entre los periodos con ID 1 y 3:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01