CODEWITHSUNDEEP
phpmysqlsql
Stefano
Stefano

Reputation: 3258

Find available time slots with SQL

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

Upvotes: 0

Views: 2702

Answers (1)

Strawberry
Strawberry

Reputation: 33945

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.

Upvotes: 1

Related Questions