如何确定商店是开放还是关闭 - 处理小时？ [英] How to find out if store open or close - dealing with hours?

问题描述

什么是在数据库中存储商店开店和关门时间的最佳方式，以及如何计算PHP中的时间？

我想出了这个表设计：

  + ---- + --------- + -------- -  + ----------- + ------------ + 
 | 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 | 
 + ---- + --------- + ---------- + ----------- + ------- ----- + 
 
 + ------------ + --------- + ------ + ----- + --------- + ---------------- + 
 |字段|类型|空| Key |默认|额外| 
 + ------------ + --------- + ------ + ----- + --------- + ---------------- + 
 | id | int（11）| NO | PRI | NULL | auto_increment | 
 | shop_id | int（11）| NO | | NULL | | 
 | week_day | int（11）| NO | | NULL | | 
 | open_hour |时间| NO | | NULL | | 
 | close_hour |时间| NO | | NULL | | 
 + ------------ + --------- + ------ + ----- + --------- + ---------------- + 
  

周二（ week_day = 2 ）下午3点开始营业，下午11点（周二）关闭。

< `week_day = 2'），它在下午6点打开，午夜2点后关闭，这将是星期四。如何在午夜（00:00:00或之后）关闭时间应该存储在数据库中？

让客户想下订单（ shop_id = 3 ）在周二下午10点，他们应该能够根据数据库数据这样做。但是如果客户想在星期四凌晨1点下订单，但数据库显示 week_day = 3 在 02:00:00

如何在PHP中编写工作，如果商店打开或没有？它似乎复杂！

我需要更改表格设计，以便在PHP中编写更容易吗？

解决方案

您可以考虑使表格信息真正正确

  ---- + --------- + ---------- + ----------- + ------------ + 
 | 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 | 2: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 
 + ---- + --------- + ---------- + ----------- + ------- ----- + 
  

然后使用以下类型

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

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?

解决方案

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'

这篇关于如何确定商店是开放还是关闭 - 处理小时？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！