Find available time slots with SQL

Question

I found an answer here on SO about the common problem of finding the available time slots in a database. The query works like a charm but it starts failing if I add a new event which starts before the alredy added events. In that case the order of the output is wrong. There is alredy a sqlFiddle example in the answer, I just added in the schema a new line:

insert into bookingEvents values (null, '2013-05-12 05:11:00', 15);

As you can see the date of the event is before the last previously inserted event's date (they're not anymore in order by timeBooked). Maybe I'm trying to put too much logic into a query, if it's like so then how could I handle this thing with PHP too?

EDIT

Probably I should first flatten the timestamps and then execute the query on the result. I found something to flatten the timestamps but I need help to merge the two queries and get something optimized for production

sorry it was a typo (jsFiddle -> sqlFiddle) :D Anyway, just look at the output of my edited fiddle and you will see that the first two lines are clearly wrong because the interval 2013-05-10 00:00:00 - 2013-05-13 13:22:00 is not all free, there is the event of my question inside — Stefano, Nov 27 '13 at 16:26

Strawberry · Accepted Answer · 2013-11-28T14:43:41.277

1

To preserve my sanity I created a view as follows...

CREATE VIEW v_bookingevents AS 
     SELECT id
          , timebooked startdate
          , timebooked + INTERVAL duration MINUTE enddate 
       FROM bookingevents 
      ORDER 
         BY startdate;

Then...

SELECT a.enddate 'Available From'
         , MIN(b.startdate) 'To'
  FROM 
     ( 

      SELECT DISTINCT
             COALESCE(LEAST(x.startdate,y.startdate),x.startdate) startdate
           , COALESCE(GREATEST(x.enddate,y.enddate),x.enddate) enddate 
        FROM v_bookingevents x 
        LEFT 
        JOIN v_bookingevents y 
          ON y.id <> x.id
         AND y.startdate < x.enddate 
   AND y.enddate > x.startdate
   UNION
   SELECT '2013-05-12 00:00:00' 
               , '2013-05-12 00:00:01' 

     ) a
  JOIN 
     (      
  SELECT DISTINCT
         COALESCE(LEAST(x.startdate,y.startdate),x.startdate) startdate
       , COALESCE(GREATEST(x.enddate,y.enddate),x.enddate) enddate 
    FROM v_bookingevents x 
    LEFT 
    JOIN v_bookingevents y 
      ON y.id <> x.id
     AND y.startdate < x.enddate 
   AND y.enddate > x.startdate

        UNION
       SELECT '2013-05-15 00:00:00' 
            ,'2013-05-15 00:00:01' 
     ) b 
    ON b.startdate > a.enddate
 GROUP 
    BY a.enddate
HAVING a.enddate < MIN(b.startdate);

http://sqlfiddle.com/#!2/e67b47/1

Obviously, you could rewrite this without the view - I just found it hard to read all those "+ INTERVAL" bits.

edited Nov 28 '13 at 14:43

answered Nov 27 '13 at 17:49

Strawberry

33,750
13
40
57

thank you for the reply, I appreciate your help. However your query doesn't work with timespans that overlap. Can you help me fix it? – Stefano Nov 28 '13 at 13:49
one more little question: if the events have a user_id property and I need to filter the free slots of the user should I put the WHERE condition in the view only? – Stefano Nov 29 '13 at 12:38
I guess you could - but that seems a little tedious. However, if we are going to persist with the view (and remember all it does is add duration to starttime), then I'd amend it to include all the user_id info too, and then amend the query to make use of that. See fiddle http://sqlfiddle.com/#!2/6341ff/10 – Strawberry Nov 29 '13 at 13:38
I don't really need the view as I edited my table and now I have start_date and end_date instead of a duration column. Maybe this discussion is going too long, I would like to get some more help in getting this query optimized but maybe this isn't the right place and we should talk in chat (if you have time). – Stefano Nov 29 '13 at 13:47
I think a better idea is to ask a new question about query optimization (not my strong point), and refer back to this thread. – Strawberry Nov 29 '13 at 13:50
as I don't need anymore the view, do I have to use the join on user_id too or can I just filter all the events with a where and let the join only on the event's id? – Stefano Nov 29 '13 at 13:52
Hm, I'll have to think about that... the problem is how to define the extents of the calendar (for time periods beyond the scope of the actual data). Other options include using a utility table (e.g. a table of all relevant dates) or handling the logic of issing dates at the application level. – Strawberry Nov 29 '13 at 13:53
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/42205/discussion-between-stefano-and-strawberry) – Stefano Nov 29 '13 at 14:00

Find available time slots with SQL

1 Answers1