Finding free appointment slots from appointment table Postgresql Rails

Question

I have an app in ruby on rails that has Appointments which belong to Providers and I am looking to create a query that finds a provider's gaps in appointments Appointments are stored as a start and finish both which are datetimes . Ideally I would call Provider.free_appointment_slots(length,start,finish) where length is the length of the appointment required and start and finish are bounds of the search ie a particular week or day.

I think it should be somewhat similar to these... How to return all the free periods of time from an appointment set Finding free blocks of time in mysql and php?

I have an appointments table with data like:

ID    |       Start       |       Finish       | ProviderID
1       2016-11-12 09:00    2016-11-12 09:30        1
1       2016-11-12 10:00    2016-11-12 10:30        1
1       2016-11-12 11:30    2016-11-12 12:00        1

etc

I would like to be able to provide a start and end time to search over and an appointment length and my query return the available appointment times.. for example If I was looking for appointments between 2016-11-12 08:30 and 2016-11-12 12:30 with an appointment length of 30min

I would receive results such as:

     Start       |     Finish
2016-11-12 08:30   2016-11-12 09:00
2016-11-12 09:30   2016-11-12 10:00
2016-11-12 10:30   2016-11-12 11:00
2016-11-12 11:00   2016-11-12 11:30
2016-11-12 12:00   2016-11-12 12:30

an additional great feature would be to be able to provide multiple search periods such as over different days

I hope this is clear any help would be much appreciated

score 0 · Answer 1 · answered Dec 05 '16 at 12:18

Ok so I managed to come up with an sql query which gives me the correct results, it will be a little bit of work to translate that into rails but it should be plain sailing from here

SELECT Available_from, Available_to
      FROM (
      SELECT @lasttime_to AS Available_from, start AS Available_to, @lasttime_to := finish
      FROM (SELECT start, finish
            FROM appointments
            WHERE finish >= '2013-11-12 08:29'
              AND start < '2016-11-12 13:01'
              AND provider_id = 1
             UNION ALL
           SELECT '2016-11-12 08:30', '2016-11-12 08:30'
             UNION ALL
           SELECT '2016-11-12 13:00', '2016-11-12 13:00'
                 ORDER BY time_from 

           ) e
      JOIN (SELECT @lasttime_to := NULL) init) x
      WHERE Available_to > DATE_ADD(Available_from, INTERVAL 15 MINUTE)

Finding free appointment slots from appointment table Postgresql Rails

1 Answers1