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'

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.