Determining available time slots(for scheduling)

If you're storing the dates as null if they are null then look for null time slots those would be available time slots. Assuming you have a datetime field.

Nope. I have the user's scheduled start/stop time & I am only storing actual scheduled events. I can see where you're going but unfortunately that would require me to create up to 8 slots per user, per day :/ I just need a good way to determine available slots from the not already scheduled time in the user's schedule. I'm writing some functions but would just like any help shortening it if any known similar solutions exist.

Would it be easier and simpler just to have them slots stored in the database for easier searching? Sometimes the way things work with pencil and paper also makes for the best way to program that.

Not really because not only can slot time vary but I can't imagine how it'd be effective to store 8 slots per 80+ users per 5 days a week per 52 weeks a year. Looking for the lack of events in a given time frame would fit much better. Here's what I'm starting with:

    $schedule = [
        'start' => '2015-11-17 09:00:00',
        'end' => '2015-11-17 05:00:00',
    ];

    $events = [
        [
            'created_at' => '2015-11-17 10:00:00',
            'updated_at' => '2015-11-17 13:00:00',
        ],
        [
            'created_at' => '2015-11-17 14:00:00',
            'updated_at' => '2015-11-17 16:00:00',
        ],
    ];

    $minSlot = '01:00.00';

Which would result in available slots at 9am, 1pm & 4pm. I'll later assign the slot time to an input which would be used to search say for 2 hour slots, but first things first :P

Out of curiosity what is the database structure for these time slots, and what would the database look like if every single slot was scheduled with something? I guess that's another way to look at it, pretend every possible slot had something already scheduled then remove just one of those scheduled appointments or whatever they are, that would leave you with a null slot. I guess I'm trying to look at it backwards.

@tjphippen Looks like you are dealing with a similar project to what I am doing. In my case it is a room reservation system and I want to display open slots for people to sign up. What I am working on is a simple loop through the 24 hours of a day and checking in 30 minute increments if a registration exists on the slot. Once I hit a spot that doesn't I keep track until the next registration. That way I can detect the gaps in the day.

I think what you have up there would fit into the same idea. I would just store the "5:00" end time as "17:00" and then do a check from "09:00" to "17:00" and do a check each look run for your event start/stop times. Then keep a counter of the loop times where you don't have a hit and once the loop is done you should have a nice array of all the "open" slots.

At least that is my plan. :) Still working on it but seem to work in theory. If I get code working I'll share but probably won't try hit it again until tomorrow.

Funny enough I'm also using fullcalendar.io to display the weekly/daily reservations.

Following this thread in case a better/more efficient idea surfaces.

I don't understand the calendar you are using the site ask for a contribution but yet they charge. To me that asking for a contribution doesn't make any sense. Have you tried to write your own event schedule instead of one that charges just curious just a question? Doesn't jquery have some sort of a calendar you can use I haven't checked the UI for that lately but they might.

Agreed @noeldiaz my thoughts exactly, I thought perhaps there could be some existing methods to better analyze time gaps I could use but guess we'll have to build it. I'm on a deadline so if I get something sooner I'll post it here.

@jlrdw the full calendar is free it's the scheduler that must be paid for. Nice they let you develop before paying though. As it does exactly what I need it to & more I have no issues with the one time $480 considering how much time it saves me.

I'm thinking with so many others trying to do similar scheduling it may make sense to develop a package for it. My needs get pretty extensive with even listing available slots by the user's location etc. My actual use is for scheduling alarm system technicians to perform installs & services but could easily be applied to many other uses.

DatePeriod ended up being the key to this & I was able to get the results I wanted quicker than I'd thought.

        $schedule = [
            'start' => '2015-11-18 09:00:00',
            'end' => '2015-11-18 17:00:00',
        ];

        $start = Carbon::instance(new DateTime($schedule['start']));
        $end = Carbon::instance(new DateTime($schedule['end']));

        $events = [
            [
                'created_at' => '2015-11-18 10:00:00',
                'updated_at' => '2015-11-18 13:00:00',
            ],
            [
                'created_at' => '2015-11-18 14:00:00',
                'updated_at' => '2015-11-18 16:00:00',
            ],
        ];

        $minSlotHours = 1;
        $minSlotMinutes = 0;
        $minInterval = CarbonInterval::hour($minSlotHours)->minutes($minSlotMinutes);

        $reqSlotHours = 1;
        $reqSlotMinutes = 0;
        $reqInterval = CarbonInterval::hour($reqSlotHours)->minutes($reqSlotMinutes);

        function slotAvailable($from, $to, $events){
            foreach($events as $event){
                $eventStart = Carbon::instance(new DateTime($event['created_at']));
                $eventEnd = Carbon::instance(new DateTime($event['updated_at']));
                if($from->between($eventStart, $eventEnd) && $to->between($eventStart, $eventEnd)){
                    return false;
                }
            }
            return true;
        }

        foreach(new DatePeriod($start, $minInterval, $end) as $slot){
            $to = $slot->copy()->add($reqInterval);

            echo $slot->toDateTimeString() . ' to ' . $to->toDateTimeString();

            if(slotAvailable($slot, $to, $events)){
                echo ' is available';
            }

            echo '<br />';
        }

Which resulted in:

2015-11-18 09:00:00 to 2015-11-18 10:00:00 is available
2015-11-18 10:00:00 to 2015-11-18 11:00:00
2015-11-18 11:00:00 to 2015-11-18 12:00:00
2015-11-18 12:00:00 to 2015-11-18 13:00:00
2015-11-18 13:00:00 to 2015-11-18 14:00:00 is available
2015-11-18 14:00:00 to 2015-11-18 15:00:00
2015-11-18 15:00:00 to 2015-11-18 16:00:00
2015-11-18 16:00:00 to 2015-11-18 17:00:00 is available

From here I'll be formatting the results into a form select in my events.create view but I'd like any suggestion anyone may have on how to improve this etc.

Out of curiosity what does these tables look like that hold the times? The table structure that is.

Currently I have an events table like:

    Schema::create('events', function (Blueprint $table) {
        $table->increments('id');
        $table->morphs('operant');
        $table->morphs('resource');
        $table->morphs('location');
        $table->string('action');
        $table->string('ip', 64)->nullable();
        $table->timestamps();
    });

I use it to store all kinds of things(customer/user action logs, timecard punches, etc) I've even considered using it to not only store our technician's jobs but their schedules as well. Using the created_at & updated_at timestamps for event starts/stops may not be the best practice but it's working well for me.

Is resource the datetime slots, if not where are the actual times / things stored?

@jlrdw I'm using the created_at & updated_at for datetimes.

updated_at is used as an event end time. I could just as easily name them start/end

Just an update, in addition to generating available time slots based on user's schedules & factors like slot duration etc. I was able to display user schedules as background events on the calendar & use them as event constraints.

At the moment I am storing user schedules like:

        "sunday" => null
        "monday" => "09:30-17:30"
        "tuesday" => "09:30-17:30"
        "wednesday" => "12:00-18:30"
        "thursday" => "09:00-17:00"
        "friday" => "09:00-17:00"
        "saturday" => null

Which requires me to first explode each day's times to generate a start & stop form input for each day on the user's profile/edit page, then reverse the process to store changes, then again when I need to use the times to generate availability etc. Does anyone know of a good way to store time ranges in the database? I'm using Postgres so I could even store them as JSON I suppose but any suggestions would be great!

Also while my current start & stop inputs for creating/modifying a user's schedule works fine I was looking for a time range selector similar to http://www.daterangepicker.com/ but found nothing. There are ones where you can select the date+time but I need only to select & store the time for each weekday.

@tjphippen: I'm currently creating a plan for a similar project, been thinking about this for the past few weeks (I've had the time to think things thru) and came up with the following:

(1) We have a user that manages one or multiple calenders each with it's own services (let's say "Wash hands, 10 minutes").

(2) Each calendar has an availability set, these are one or multiple timespans per given date. If the user is available each monday to friday between 13:00 and 18:00 for a whole year, that results in 52 * 5 = 260 records for availability. This is too much, so I'm going to have each calender have it's default settings for monday to friday (multiple slots per day) and then another table containing the availability that overwrites the default settings (other times, closed days, etc). This keeps the table with the default times as little as possible (7 days x 2 timespans (if a lunchbreak is present in the day for example) per calender). Having 30 days that aren't according the default plan that reduces the amount of availability-records from 260 to about 50.

(3) Then there's the appointment table containing all appointments (like your events).

At first: thanks for pointing me out to DatePeriod, that'll take on headache away from defining the available slots in the calendar. Huge help, thanks!

Second: my biggest issue before, and still, is how to determine if a date is fully booked. I want to reduce the amount of queries needed as much as possible, but my first guess would be to do a calculation for each day in the calender, much alike finding a free slot on a day. But that would be a huge proces to calculate 31 days ahead.

What I'm leaning to is to have the script take the service that takes the least amount of time and perform an availability check on the day that just has been booked. Then, if there's no spot, an extra record should be generated in a table that locks out the date. Down side to this is that the day would stay available even if a user selects the service with the highest amount of time needed. For instance: when the service with the least amount of time needed (10 minutes) is still available on day, it'd be green even if there's a 20 minute during service is selected.

Can't really get my mind wrapped about the fatest (performance-wise) way to achieve this.

@Pendo maybe we could both contribute the the same package on this one. Currently I already have a simple but well working scheduling system where each user has an assigned schedule object. Each schedule defines ranges of hours for the weekdays, then based on those hours & they type/duration of a job that needs to be scheduled I have available slots being generated quite well.

@tjphippen: sure, if I can be of any help let me know! I'm just taking a break from setting op a helper class that handles all checks for my application. I took a lot of code from your example and added some extra things to it to fit my needs, for example:

(1) A day can have multiple timeIntervals (eg. 8.00-12.00 & 13.00-18.00), so I'd have to loop the function for each interval

(2) I have a function that returns availability per day (1 free spot? day available, 0 free spots? unavailable) instead of a full list of slots

(3) A day can be closed (which would return false on that given day)

Besides that my application is setup a little bit diferent ofcourse, but I'd be happy to share what I made of it.

Sure I'd love to see what you came up with & I'm glad I was able to help you get going with it :)

Just thought I would drop something in in case its useful. The following conditional tells you if two time periods overlap:

$start_time < $booking->end_time && $end_time > $booking->start_time

Have a look at dhtmlx.com - their (javascript) based scheduler would do a lot of that for you - it DOES interface with Laravel connector "PHPLaravelDBDataWrapper", although needs minor tweaks for Laravel 5.3. Higher versions offer "collision detection" which is the issue you were referring to, and it doesn't need to store every available hour! HTH

A(start) = 13:30 A(end) = 14:00 B(start) = 14:00 B(end) = 14:30

A(start) < B(end) == false && A(end) > B(start) == false

Therefore they do not overlap? Whats wrong with the equation? @Pendo

Further, looking at your "between" condition

A(start) = 13:30 A(end) = 15:30 B(start) = 14:00 B(end) = 14:30

A(start) < B(end) == true && A(end) > B(end) == true

Therefore they overlap