Allen's Interval Algebra operations in SQL

Here is a SQLFiddle demoFirst of all create temp tables to simplify queries though you can put these creation queries into final queries and do it without temp tables:

create table t as select * from(select null s ,"start"-1 as e  from dataunion allselect "start" s,null e  from dataunion allselect "end"+1 s ,null e  from dataunion allselect null s ,"end" e  from data) d where exists (select "start"                   from data where d.s between data."start" and data."end"                               or d.e between data."start" and data."end"                                );--Operation 1 - Disjoined Result   create table t1 as select s,e,e-s+1 width from(select distinct s,(select min(e) from t where t.e>=t1.s) e from t t1) t2 where t2.s is not null and t2.e is not null;--Operation 2 - Reduced Resultcreate table t2 as select s,e,e-s+1 width from(select s,(select min(d2.e) from t1 d2 where d2.s>=d.s and not exists          (select s from t1 where t1.s=d2.e+1) ) efromt1 d where not exists(select s from t1 where t1.e=d.s-1) ) t2;--Temp table for Operation 3 - Gapscreate table t3 as select null s, s-1 e from t2union allselect e+1 s, null e from t2;

Now here are queries:

--Operation 1 - Disjoined Resultselect * from t1 order by s;--Operation 2 - Reduced Resultselect * from t2 order by s;--Operation 3 - Gapsselect s,e,e-s+1 width from(select s,(select min(e) from t3 where t3.e>=d.s) e from t3 d) t4 where s is not null and e is not nullorder by s;