availablily.sql

CREATE TABLE availability (
    dates tstzrange NOT NULL
);

CREATE TABLE reservation (
    dates tstzrange NOT NULL
);

COPY availability (dates) FROM stdin;
["2014-01-01 00:00:00+01","2014-01-10 00:00:00+01"]
["2014-01-15 00:00:00+01","2014-01-20 00:00:00+01"]
\.

COPY reservation (dates) FROM stdin;
["2014-01-01 00:00:01+01","2014-01-05 00:00:00+01"]
["2014-01-05 00:00:01+01","2014-01-07 00:00:00+01"]
["2014-01-16 00:00:01+01","2014-01-18 00:00:00+01"]
\.




with res as (select a.dates as out, r.dates as in from availability a left join reservation r on a.dates && r.dates),
around as (select
  greatest(
    lower("in") - lead(upper("in"), 1, lower("out")) over (partition by out order by "in" desc),
    lag(lower("in"), 1, upper("out")) over (partition by out order by "in" desc) - upper("in")
  ) as intvl
  from res)

select max(coalesce(intvl, upper(dates) - lower(dates))) from availability left join around on availability.dates = around.out;