CODEWITHSUNDEEP
phpmysqlsqlwhere-clause
nimrod
nimrod

Reputation: 5732

Check if appointment time fits users working time with SQL - Wrong proposal during night

I defined hours where an employee is working aka working hours, e.g.

$start = "09:00:00";
$end = "18:00:00";

A user can now select an appointment, and the query should validate it, e.g.

$appointment_start = "09:00:00";
$appointment_end = "10:00:00";

My SQL query should now check if the employee is working at that specific time. If that is the case, propose this appointment to the user. This is how I do it:

...
AND w.start <= '$appointment_start' 
AND w.end >= '$appointment_end';

There seems to be a problem during night when the day changes, e.g. when start is 23:00:00 and end is 00:30:00. This should not be a valid appointment time, but my query proposes it:

start substr: 23:00:00 || end substr: 00:00:00 
start substr: 23:30:00 || end substr: 00:30:00

How do I have to change the WHERE statement of my SQL query to fix this issue?

Upvotes: 1

Views: 160

Answers (2)

Diver
Diver

Reputation: 1608

If you are unable to use a datetime you could massage your values.

$appointment_end = ($appointment_end < $appointment_start) ? $appointment_end + [24hours] : $appointment_end;
$end  = ($end  < $start) ? $end  + [24hours] : $end;

Basically if the end time is less than the start time, assume it's the next day and add 24 hours, then do the check as normal. (not sure of the syntax in php to add the [24hours])

Upvotes: 0

willy
willy

Reputation: 1490

You could use a datetime rather than just a time to avoid this issue altogether.

Or you could do something like:

    AND (( w.start < w.end
           AND w.start <= '$appointment_start'
           AND w.end >= '$appointment_end') OR
         ( w.start > w.end
           AND w.start >= '$appointment_start'
           AND w.end <= '$appointment_end' ) )

Basically, you invert your comparison operators when start happens after end.

Upvotes: 1

Related Questions