Find is a business is open: MySQL hours calculation.

Question

I have a list of business stored in a locations table, and stored in that table are hours the business opens and closes:

location
`mon_1_open`
`mon_1_closed`
`tue_1_open`
`tue_1_closed`
`wed_1_open`
`wed_1_closed`

ect...

I store the times in full hours and minutes, so say a business is open from 9:00AM to 5:30PM on monday.. mon_1_open = '900' AND mon_1_closed = '1730'.

I can't seem to figure out a way to find the day of week and output if the business is else open or closed based on the time of day.

Any suggestions?

Answer 1

$dayOfWeek = strtolower(date('D'));

$query = '
    SELECT
        location,
        '.$dayOfWeek.'_1_open <= '.date('Gi').' AND
        '.$dayOfWeek.'_1_closed >= '.date('Gi').' as is_open';

That should work.

However, you really should use a proper time datatype for the open/closed columns.

Answer 2

This does not necessarily answer your question, but it may in the long run.

Your database scheme seems flawed. It definitely is not normalized. I would address that before it becomes a big issue, as you have noticed that it makes it hard to locate certain businesses hours. Here is a draft scheme that might be better suiting.

TABLE: locations
    id INT AUTO_INCREMENT PRIMARY KEY
    name VARCHAR(50) 

TABLE: location_hours
    id INT AUTO_INCREMENT PRIMARY KEY
    location_id INT  -  Foreign Key references locations table
    day CHAR(3) - (examples: mon, tue, wed, thu, fri, sat, sun)
    hours VARCHAR(4) - (could also be int)

Then to get todays date, this can be done in MySQL with DATE_FORMAT %a, an example query:

SELECT locations.name, location_hours.hours 
FROM locations 
    JOIN location_hours ON locations.id = location_hours.location_id 
WHERE location_hours.day = DATE_FORMAT(NOW(), '%a') 
    AND location.name = 'Someway Business'
ORDER BY location_hours.hour

You should not need an open / close given that the the ORDER BY knows that 0900 < 1430 since it is a VARCHAR (although INT should know how to sort it as well), but your code when adding businesses will either need to update this record or you will need another field active to signify if that row should be used in the query. Just remember to use 24 hour time. Again this is a mock up, I just created it on the spot so it probably could use some improvements, but that would be better then doing a hack like you would have to with your current code.

UPDATE

Addressing the comment about finding if it is open or close:

Just use the PHP date function and call date('Hi') this will pull out the current time in 24-hour time, then you just do a simple if statement to see if it is between that, if it is, it is opened.

IE:

$sql = "SELECT locations.name, location_hours.hours 
FROM locations 
    JOIN location_hours ON locations.id = location_hours.location_id 
WHERE location_hours.day = DATE_FORMAT(NOW(), '%a') 
    AND location.name = 'Someway Business'
ORDER BY location_hours.hour";

$result = mysql_query($sql) or trigger_error("SQL Failed with Error: " . mysql_error());

$times = array();
while ($row = mysql_fetch_assoc($result)) {
    if (empty($times['open'])) {
        $times['open'] = $row['hours'];
    }else {
        $times['closed'] = $row['hours'];
    }
}

$currentTime = date('Hi');

if ($times['open'] <= $currentTime 
        && $times['closed'] > $currentTime) {
    echo "Open";
}else {
    echo "Closed";
}

Given that my logic is correct. Again, this is just pseudo code an example of usage. Given I just wrote it up on the spot. The above assumes you are only querying one business at a time.