Determine if the store is open?

Question

In PHP and MySQL - how to determine if the Store is Open or Close (return true or false)?

Also how to get the next opening hours if the store is closed?

Example of Opening_Hours table:

+----+---------+----------+-----------+------------+---------+
| id | shop_id | week_day | open_hour | close_hour | enabled |
+----+---------+----------+-----------+------------+---------+
|  1 |       1 |        1 | 16:30:00  | 23:30:00   |       1 |
|  2 |       1 |        2 | 16:30:00  | 23:30:00   |       1 |
|  3 |       1 |        3 | 16:30:00  | 23:30:00   |       0 |
|  4 |       1 |        4 | 16:30:00  | 23:30:00   |       1 |
|  5 |       1 |        5 | 10:00:00  | 13:00:00   |       1 |
|  6 |       1 |        5 | 17:15:00  | 00:30:00   |       1 |
|  7 |       1 |        6 | 17:15:00  | 01:30:00   |       1 |
|  8 |       1 |        7 | 16:30:00  | 23:30:00   |       0 |
+----+---------+----------+-----------+------------+---------+

The open_hour and close_hour are TIME type fields. Table design ok?

Example of current times:

• Current time: Tue 23:00, - Output: Open, 'Open at Tue 16:30 - 23:30'

• Current time: Tue 23:40, - Output: Close, 'Open at Thur 16:30 - 23:30'

Open on Thursday because Opening_Hours.week_day = 3 is disabled

---

Now how to handle the midnight time? This get more complicated.

As you can see, on Saturday (Opening_Hours.week_day = 5), it is open from 17:15 PM to 01:30 (closed next day Sunday)

If the current time is Sunday 01:15 AM, then the store would still be open base on Opening_Hours.week_day = 5.

Output: Open, 'Open at Sat 17:15 - 01:30'

Vote

Please don't post multiple questions at once. Brake your problem into smaller parts and ask specifics. Also this looks like homework which is not welcomed here ... – Turfman

@JanHančič It is NOT multiple questions at once. My question is how to find out store is open or closed. Secondly this is NOT homework. – Vote

It looks like homework to me, but if you say it isn't then I believe you :) – Turfman

Answer 1

In the past, I've handled this by using a time stamp without a date (seconds since midnight). So for Saturday, the open would be 62100 and the close would be 91800.

My thought was this removes some of the logic needed when a close crosses midnight, as you only need to compare the seconds since the start of the date to the time range.

And it's pretty easy to check if it's still open from 'yesterday' - just add 86400 to the current 'time' (seconds since the start of the day) and check against the previous day.

Probably all a single SQL statement.

Grogshop

Answer 2

You can use the PHP date() function and compare it to your opening hours.

You can do something like this recursive function (not working PHP code, but PHP combined with pseudo-code):

/* $current_time should be in the format of date("His") */
function check_hours($current_day, $current_time)
{
    Get the MySQL row for today here

    if (Opening_Hours.enabled == 1 WHERE Opening_Hours.week_day == $current_day)
    {
        if ((date("His") >= Opening_Hours.open_hour) and ($current_time <= Opening_Hours.close_hour))
        {
            // convert_numeric_day_to_full_representation isn't a real function! make one
            return 'Open: ' . convert_numeric_day_to_full_representation($current_day) . ' ' . Opening_Hours.open_hour . ' – ' . Opening_Hours.close_hour;
        }
        elseif (date("His") < Opening_Hours.open_hour)
        {
            return 'Closed: Next opening hours: ' . convert_numeric_day_to_full_representation($current_day) . ' ' . Opening_Hours.open_hour . ' – ' . Opening_Hours.close_hour;
        }
        else
        {
            return check_hours($tomorrow, '000000');
        }
    }
    else
    {
        return check_hours($tomorrow, '000000');
    }
}

Pyrene