CODEWITHSUNDEEP
mysqldatabasepostgresqldatabase-design
stUrb
stUrb

Reputation: 6842

(my)SQL only solution to show available time slots from agenda system

Description I'm making a agenda system with (multiple) agenda's where you can reserve a time slot of a variable time (10/15/20 or 30 minutes) during a selected period of time (eg. 9:00am till 11:00am on mondays and 10:30am till 3:00pm)

Off course I can do this in PHP and poll the appointments and availability table for every time slot available for every date being showed, but some how that sounds a little bit server intensive. So I was wondering if there was a way to produce a mySQL result relation which shows the time-slots and availability tupples....

Database/table setup This is the table setup which I thought was necessary.

appointments holds the appointments already made
id | int unique id
agenda_id | int unique id identifying the agenda
start | date_time start of the appointment
end | date_time end of the appointment
some more descriptive fields

availability when are there which time slots available
agenda_id | int identifying which agenda
day_of_the_week | int which day, eg 1=monday
start_time | time e.g. 09:00:00 end_time | time e.g. 11:00:00
time_slot | int how long are the time slots? e.g. 10minutes

( remarks:
If necesssary the time_slots field can also be hard coded;
The system runs on a MySQL database, if necessary we can switch to postgresql
)

Desired result Given a date 2013-02-21 and an agenda_id 1 (and if necessary the time slot 15) and a working day from 8:00 to 18:00;
how can I create a mysql query, view or stored procedure to produce the following table/relation:

date       | 8:00 | 8:15 | 8:30 | 8:45 | 9:00 | 9:15 | ..... | 17:15 | 17:30 | 17:45 |
2013-02-21 |   0  |  0   |   0  |  0   |  1   |   2  | ..... |   2   |   2   |   1   |

Where:
0 = not bookable
1 = available, you can book this timeslot
2 = not available, an appointment exists

Upvotes: 0

Views: 2297

Answers (2)

kgrittn
kgrittn

Reputation: 19501

If you do decide to switch to PostgreSQL, you probably want to make sure you're on version 9.2 (or later), so that you can take full advantage of range types and exclusion constraints. A GiST index is very useful with this sort of data, but an exclusion constraint will automatically create one, so you probably don't need to declare one explicitly. If you want to list available openings within a certain range, the generate_series function could be used to join candidate times against the existing schedule with NOT x && y to filter out the unavailable times.

I'm not sure what the equivalent is in MySQL.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You want to pivot the appointments and compare to the availability. This is a join and aggregation query, with conditional aggregations. The following is the idea:

select agendaId, const.thedate,
       sum(case when thedate + interval 8 hour + interval 0 minute between ap.start and ap.end
                then 1 else 0
           end) as "8:00",
       sum(case when thedate + interval 8 hour + interval 15 minute between ap.start and ap.end
                then 1 else 0
           end) as "8:15",
       . . .
from (select date('2013-02-21' ) as thedate) const cross join
     Availability a left outer join
     Appointments ap
     on a.AgendaId = ap.AgendaId and
        const.day_of_the_week = weekday(const.thedate)        
where date(ap.start) = const.thedate 
group by AgendaId

Upvotes: 0

Related Questions