How to find out if store open or close - dealing with hours?

Question

What is the best way to store shop opening and closing time in the database and also how to calculate the time in PHP?

I have come up with this table design:

+----+---------+----------+-----------+------------+
| id | shop_id | week_day | open_hour | close_hour |
+----+---------+----------+-----------+------------+
|  1 |       3 |        1 | 15:00:00  | 23:00:00   |
|  2 |       3 |        2 | 15:00:00  | 23:00:00   |
|  3 |       3 |        3 | 18:00:00  | 02:00:00   |
|  4 |       3 |        4 | 18:00:00  | 02:00:00   |
|  5 |       3 |        5 | 18:00:00  | 03:00:00   |
+----+---------+----------+-----------+------------+

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| shop_id    | int(11) | NO   |     | NULL    |                |
| week_day   | int(11) | NO   |     | NULL    |                |
| open_hour  | time    | NO   |     | NULL    |                |
| close_hour | time    | NO   |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

For example, on Tuesday (week_day = 2) it open at 3PM and close at 11PM (Tuesday).

On Wednesday (`week_day = 2'), it open at 6PM and close after midnight at 2AM which would be Thursday. How should midnight (00:00:00 or after) closing time should be stored in the database?

Let say customer want to place an order (shop_id = 3) at 10PM on Tuesday, they should be able to do so according to the database data. However if customer want to place an order at 1AM on Thursday but the database show that week_day = 3 it close at 02:00:00

How to write in PHP to work out if the shop open or not? it seem complicated!

Do I need to change the the table design so it would much easier to write in PHP?

Answer 1

I guess you could do a SQL-question like this, and check if the query return any results:

SELECT * FROM opening_hours_table WHERE shop_id = your_shop_id AND week_day = WEEKDAY(NOW()) + 1 AND open_hour < NOW() AND close_hour > NOW()

If you get a result back, then your within opening hours.

Edit: Made a few syntax-corrections to the SQL.

Answer 2

You can consider making the table information truly correct

+----+---------+----------+-----------+------------+
| id | shop_id | week_day | open_hour | close_hour |
+----+---------+----------+-----------+------------+
|  1 |       3 |        1 | 15:00:00  | 23:00:00   |
|  2 |       3 |        2 | 15:00:00  | 23:00:00   |
|  3 |       3 |        3 | 18:00:00  | 23:59:59   |
|  4 |       3 |        4 | 00:00:00  | 02:00:00   |
|  5 |       3 |        4 | 18:00:00  | 23:59:59   |
|  6 |       3 |        5 | 00:00:00  | 02:00:00   |
|  7 |       3 |        5 | 18:00:00  | 23:59:59   |
|  8 |       3 |        6 | 00:00:00  | 03:00:00   |
+----+---------+----------+-----------+------------+

Then use the following kind of (this query is for Tuesday 10:00PM as you mentioned):

SELECT count(*) FROM `shop` 
 WHERE week_day=3 
 and open_hour<='22:00:00' 
 and close_hour>='22:00:00'