Checking if booking is possible in SQL (Postgresql)

Question

I have table booking with such looking records:

  id  |        from         |         to
------+---------------------+---------------------
  101 | 2015-09-24 08:00:00 | 2015-09-24 09:30:00
 2261 | 2015-09-24 09:00:00 | 2015-09-24 10:00:00
 4061 | 2015-09-24 10:00:00 | 2015-09-24 10:30:00
  204 | 2015-09-24 12:00:00 | 2015-09-24 13:30:00
 2400 | 2015-09-24 13:30:00 | 2015-09-24 14:00:00
 4224 | 2015-09-24 14:00:00 | 2015-09-24 14:30:00
  309 | 2015-09-24 16:00:00 | 2015-09-24 17:30:00
 2541 | 2015-09-24 17:00:00 | 2015-09-24 18:00:00

I am looking for optimal query to find anwser to question:

Is this possible to find a timeslot with duration x (ie. 30 minutes) in above records?

I have ideas to use postgres arrays or time ranges, but still looking for better ideas....

EDIT: I will provide 'fake' bookings as boundaries, but if you have ideas how to do it better, please write :)

Answer 1

Something like this:

select t1.*
from tablename t1
where (select min("from") from tablename t2
       where t2."from" > t1."from") >= t1."to" + interval '30' minute

I.e. return a row if the gap to the following row is >= 30 minutes.

Note: from and to are reserved words in ANSI SQL, that's why they are delimited as "from" and "to".

Answer 2

Here's one solution using analytical functions, it provides all windows with no bookings:

SELECT null as ts_from, min(ts_from) as ts_to
  FROM bookings
 UNION ALL
SELECT ts as ts_from, next_ts as ts_to
  FROM (SELECT ts, lead(ts, 1) over (order by ts) as next_ts, sum(bk) over (order by ts) as bksum
          FROM (SELECT ts_from as ts, 1 as bk
                  FROM bookings
                 UNION ALL
                SELECT ts_to as ts, -1 as bk
                  FROM bookings) as t) as tt
 WHERE bksum = 0
 ORDER BY 1 NULLS FIRST;

SQL Fiddle here.