设计运行时间SQL表
我正在设计一个 SQL 表来存储商店的营业时间。
有些商店的营业时间非常简单：周一至周日上午 9:30 至晚上 10:00
其他商店则稍微复杂一些。请考虑以下场景：
Monday: Open All Day
Tuesday: 7:30AM – 2:30PM & 4:15PM – 11:00 PM
Wednesday: 7:00PM – 12:30 AM (technically closing on Thursday morning)
Thursday: 9:00AM – 6:00PM
Friday: closed.
您将如何设计表格？
编辑
营业时间将用于显示商店是否在用户选择的时间营业。
不同的表可能可以处理任何异常，例如假期。
商店的营业时间每周不会改变。
I am designing a SQL table to store hours of operation for stores.
Some stores have very simple hours: Monday to Sunday from 9:30AM to 10:00PM
Others are little more complicated. Please consider the following scenario:
Monday: Open All Day
Tuesday: 7:30AM – 2:30PM & 4:15PM – 11:00 PM
Wednesday: 7:00PM – 12:30 AM (technically closing on Thursday morning)
Thursday: 9:00AM – 6:00PM
Friday: closed.
How would you design the table(s)?
EDIT
The hours will be used to showing if a store is open at a user selected time.
A different table can probably handle any exceptions, such as holidays.
The store hours will not change from week to week.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这样的表格对于您发布的输出来说很容易，并且只需稍微回击一下（打开？是/否）：
特点：
要查询数据集，只需：
SELECT Day, Open, Close...（显然你想要格式化 Open/Close）要查询 IsOpen?，只需：
A table like this would be easy for both the output you posted, as well as just firing a bit back (open? yes/no):
Features:
To query for your dataset, just:
SELECT Day, Open, Close...(you'd want to format Open/Close obviously)To query IsOpen?, just:
更多地将其视为定义时间范围，天/周更复杂，因为它们有规则并定义了开始和停止。
您将如何定义时间范围？
一个约束（开始[时间和日期]），一个参考 >“持续时间”（小时、分钟……跨度）*。现在，班次（时间范围）可以跨越多天，您无需使用复杂的逻辑来提取和在计算中使用数据。
Think of it more as defining time frames, days / weeks are more complex, because they have rules and defined start and stops.
How would you define a timeframe?
one constraint (Start[Time and Day]), one reference 'Duration' (hours, minutes,.. of the span)*. Now the shifts (timeframes) can span multiple days and you don't have to work complex logic to extract and use the data in calculations.
除了存储和展示之外，您还需要做更多的事情吗？
我认为一个需要告诉商店在特定时间是否营业的设计必须了解所有的可能性，否则，你最终将无法容纳某些东西。
假期例外怎么办？
我会考虑将它们存储为基于基准时间的间隔（从一周时间 0 开始的分钟数）。
所以 0 是星期一的午夜。
间隔 1 为 0 - 1440，
间隔 2 为 1890 - 2310
，等等。
您可以轻松地将用户选择的时间转换为分钟偏移量，并确定商店是否营业。
剩下的唯一问题是显示中的解释，以便友好显示（可能是一些广泛的逻辑，但并非不可能）以及时间 10080 -> 的重叠。 0。
Do you have to do more than just store and display it?
I think a design which needs to tell if a store is open at a particular time would have to be informed by all of the possibilities, otherwise, you will end up not being able to accommodate something.
What about holiday exceptions?
I would consider storing them as intervals based on a base time (minutes since time 0 on a week).
So 0 is midnight on Monday.
Interval 1 would be 0 - 1440
Interval 2 would be 1890 - 2310
etc.
You could easily convert a user selected time into a minute offset and determine if a store was open.
Your only problem remaining would be interpretation in display for friendly display (probably some extensive logic, but not impossible) and overlap at time 10080 -> 0.