Suggest next available date for booking

Question

I have a table called bookings, that have the following columns & data:

ID | Client | Start At          | End At
1    103      2020-12-17 14:15    2020-12-17 14:30
2    202      2020-12-17 14:35    2020-12-17 15:00
3    204      2020-12-17 16:00    2020-12-17 16:20
4    158      2020-12-17 17:00    2020-12-17 18:00
5    157      2020-12-19 10:00    2020-12-19 10:15
6    142      2020-12-21 10:00    2020-12-21 10:15

The creation of bookings have the following rules:

Sundays are not available
Monday to friday availability from 10am to 7pm
Saturday availability from 10am to 8pm
Bookings duration is dynamic (can be 5 minutes, 30min, 1h, 2h, etc)

So far my code only checks if the certain date is occupied. Lets say I want to check if the date 2020-12-17 14:20 to 2020-12-17 14:30 is available.

SELECT * 
FROM bookings 
WHERE 
(
    (start_at <= '2020-12-17 14:20' AND end_at >= '2020-12-17 14:20') OR 
    (start_at <= '2020-12-17 14:30' AND end_at >= '2020-12-17 14:30') OR 
    (start_at >= '2020-12-17 14:20' AND end_at <= '2020-12-17 14:30')
)

This works great and would return that the date is not available (as you can see in the ID 1).

What I need:

To suggest my users the next available date taking in consideration the rules
To suggest the closest next date after the selected date

So, lets have the following example:

User selects date 2020-12-17 14:40 to 2020-12-17 15:00 [20 min] [Occupied] [Suggest: 2020-12-17 15:00]
User selects date 2020-12-17 18:00 to 2020-12-17 19:15 [1h15 min] [Occupied] [Suggest: 2020-12-18 10:00]
User selects date 2020-12-21 10:10 to 2020-12-21 10:20 [10 min] [Occupied] [Suggest: 2020-12-21 10:15]
User selects date 2020-12-21 12:00 to 2020-12-21 12:20 [20 min] [Free]

Can I achieve this with plain SQL or do I need to put some PHP logic behind it?

I mean, i think it's possible maybe also with SQL, but i'm not sure about the fact that will be maintainable — Alberto Sinigaglia, Dec 16 '20 at 16:34
also, i found this https://stackoverflow.com/questions/5124386/help-with-sql-query-to-find-next-available-date-for-a-reservation-system that might be helpful for you — Alberto Sinigaglia, Dec 16 '20 at 16:36
How should these suggestions be formed? Next available term that's large enough? Or perhaps closest (considering terms before the selected one, not just after)? — El_Vanja, Dec 16 '20 at 16:37
Incidentally, the rule for overlaps is as follows: Event A overlaps Event B if Event A starts before Event B ends, and ends after Event B starts. — Strawberry, Dec 16 '20 at 16:49
@El_Vanja The closest would be better and I only need the first suggestion — Linesofcode, Dec 16 '20 at 16:55
And I think you should amend the question to clarify that you want the closest result (regardless of whether that's before or after the selected dates) — Strawberry, Dec 16 '20 at 16:57
@Strawberry actually, now that I think about, it should be the closest after the date selected, otherwise it's gonna suggest always the same date! I'm gonna rectify. — Linesofcode, Dec 16 '20 at 17:02

score 0 · Accepted Answer · answered Dec 18 '20 at 18:21

Solved.

The algorithm to grab a suggested date is basically a endless loop until we find a space available in the bookings.

In my case there are some rules to apply:

Monday to Friday = bookings from 10am to 7pm
Saturday = bookings from 10am to 8pm
Sunday = bookings not allowed
Bookings can have dynamic range (start <-> end)

Taking this rules in consideration:

$suggestedDate = '';
$startAt = '2020-12-18 10:00';
$endAt = '2020-12-18 10:45';

do 
{
    // The SQL code is in the original post 
    $isAvailable = SQL('...');
    
    if ($isAvailable)
        $suggestedDate = $startAt;
    else
    {
        $_startAt = new DateTime($startAt);
        $_endAt = new DateTime($endAt);
        $diff = $_endAt->diff($startAt);
        
        // Calculates the time difference in minutes (converts the hours in minutes as well, so 1h15 = 75min)
        $minutes = $diff->h * 60;
        $minutes += $diff->i;
        
        /**
         * Input:
         *  - Start At: 2020-12-18 10:00    End At: 2020-12-18 10:45    Minutes: 45
         * 
         * Output:
         *  - Start At: 2020-12-18 10:45    End At: 2020-12-18 11:30    Available? No
         *  - Start At: 2020-12-18 11:30    End At: 2020-12-18 12:15    Available? Yes!
         */
        $startAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($startAt)));
        $endAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($endAt)));
        $dayOfWeek = date('l', strtotime($startAt));
        $isMaxTime = false;
        
        // Grabs for the days of the week the max time
        if (in_array($dayOfWeek, ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']))
            $maxTime = date('Y-m-d 19:00', strtotime($startAt));
        else if ($dayOfWeek == 'Saturday')
            $maxTime = date('Y-m-d 20:00', strtotime($startAt));
            
        $_endAt = new \DateTime($endAt);
        $_maxTime = new \DateTime($maxTime);
        
        // Will check if the `endAt` is bigger than the maximum allowed time
        if ($_endAt > $_maxTime)
            $isMaxTime = true;
        
        /**
         * Here we just check if the day is Sunday or if the maximum time has been reached. In both situations
         * all we have to do is increment one day & start all over again, but starting at 10am!
         */
        if ($dayOfWeek == 'Sunday' || $isMaxTime)
        {
            $startAt = date('Y-m-d 10:00', strtotime("+1 day", strtotime($startAt)));
            $endAt = date('Y-m-d 10:00', strtotime("+1 day", strtotime($endAt)));
            $endAt = date('Y-m-d H:i', strtotime("+{$minutes} minutes", strtotime($endAt)));
        }       
    }   
} while (empty($suggestedDate));

echo $suggestedDate;

Suggest next available date for booking

What I need:

1 Answers1