blocks|key|3319081|text|总体而言，我认为这没有什么错。除了..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3319082|3319083|我会将星期几存储为一个整数，使用您的本地编程语言使用的任何编号系统(在其库中)。这将减小数据库的大小，并从代码中删除字符串比较。|ordered-list-item|3319084|我可能会将业务表的外键放在这个表中。这样你就不需要链接表了。|3319085|3319086|所以我想我会这么做：|3319087|CREATE+TABLE+"business_hours"+(
+++++"id"+integer+NOT+NULL+PRIMARY+KEY,
+++++"business_id"+integer+NOT+NULL+FOREIGN+KEY+REFERENCES+"businesses",
+++++"day"+integer+NOT+NULL,
+++++"open_time"+time,
+++++"close_time"+time
)|code-block|syntax|javascript|3319088|在我的业务逻辑中，我会强制要求每个“业务”至少有7个“营业时间”。(至少因为乔恩·斯基特是对的，你可能想要度假时间。)尽管你可能想要放松这个限制，简单地把“营业时间”放在关闭的日子里。|3319089|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|W|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Y|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|Z|8|@]|9|@]|A|$N|O]]|$1|P|3|Q|5|6|7|10|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|S|$]]

Overall, I see nothing wrong with this. Except...

<ol>
<li>I would store the day of week as an integer using whatever numbering system your native programming language uses (in its libraries). This will decrease the size of the database and remove string comparisons from your code.</li>
<li>I would probably put the foreign key to the business table right here in this table. That way you won't need a link table.</li>
</ol>

So I guess I would do:

<pre><code>CREATE TABLE "business_hours" (
 "id" integer NOT NULL PRIMARY KEY,
 "business_id" integer NOT NULL FOREIGN KEY REFERENCES "businesses",
 "day" integer NOT NULL,
 "open_time" time,
 "close_time" time
)
</code></pre>

In my business logic, I would enforce a constraint that every "business" has at least 7 "business hours". (At least because Jon Skeet is right, you might want holiday hours.) Though you may want to relax this constraint by simply leaving off "business hours" for days that the business is closed.

blocks|key|3319181|text|这在某种程度上取决于你需要存储它的目的，以及现实世界的数据可能是什么样子。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3319182|如果您需要能够确定业务是否在某一点上开放，那么查询计划可能有点笨拙。然而，更重要的是:你需要满足中午关闭的需求吗？|3319183|一些选项包括；|3319184|3319185|类似于您已有的方案，但可以选择在同一天有多个期间。它可以满足午休时间的需要，但是运行一个查询来给出给定一天的开放时间，比如展示给用户。|unordered-list-item|3319186|A位图风格的方法；9-5的"000000000111111110000000“。这种方法的缺点是，您必须选择特定的粒度，即整个小时或半小时，甚至是分钟。粒度越细，人类读取数据的难度就越大。您可以使用按位运算符将此值存储为单个数字，而不是整数字符串，但这再次损害了易读性。|3319187|3319188|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|S|8|@]|9|@]|A|$]]|$1|J|3|K|5|I|7|T|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

It sort of depends on what you need to store it for and what the real-world data could look like. 
If you need to be able to determine if the business is open at a certain point then it may be a bit awkward to query the scheme as laid out. More importantly, though, is: Would you ever need to cater for a mid-day closure? 

Some options include; 

<ul>
<li>A scheme like what you have, but with the option to have multiple periods for the same day. It would cater for the lunch break, but would make it awkward to run a query that gives you the opening hours for a given day, say for presentation to a user.</li>
<li>A bitmap style approach; "000000000111111110000000" for 9-5. The downside to this approach is that you have to choose a specific granularity, i.e. whole hours or half-hours or, indeed, minutes. The finer the granularity, the harder the data is to read for a human. You could use bitwise operators to store this value as a single number rather than a string of integers, but again it hurts legibility.</li>
</ul>

blocks|key|3106443|text|可以考虑在假日中加入额外的字段，包括月份/月日/月份的星期。月份的星期有一些细微的微妙之处，例如，根据年份的不同，“最后”可以是第4周或第5周。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3106444|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Might think about factoring in holidays by including additional fields for month of year/day of month/week of month. Week of month has some minor subtlties "last" could for example be week 4 or 5 depending on the year.

blocks|key|3319329|text|此模式未涵盖的一种情况是一天中有几个开场期。例如，当地酒吧的营业时间为12:00-14:30和17:00-23:00。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3319330|也许一家剧院的票房可以用来看日场和晚上的演出。|3319331|在这一点上，您需要决定是否可以在同一天有多个条目，或者是否需要在同一行中表示不同的小时。|3319332|那跨过午夜的营业时间呢？假设酒吧营业时间是19:00-02:00。您不能只将打开和关闭时间与您想要测试的时间进行比较。|3319333|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

One situation that isn't covered by this schema is several opening periods in a day. For example, the local pub is open 12:00-14:30 and 17:00-23:00.

Maybe a theatre box office is open for a matinee and an evening performance.

At that point you need to decide if you can have several entries for the same day, or if you need to represent different hours in the same row.

What about opening times that cross midnight. Say a bar is open 19:00-02:00. You couldn't just compare the opening and closing times with the time you want to test.

blocks|key|338364|text|我了解到，如果你想让google数据标记识别你的数据，你应该遵循以下指导原则：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338365|https://schema.org/openingHours|offset|length|338366|http://schema.org/OpeningHoursSpecification包含“有效日期”，这对于某些业务非常有用。|338367|https://schema.org/docs/search_results.html#q=hours|338368|没有主键应该没问题，除非您允许企业与连接表共享相同的时间-有趣的是，最终您将有有限数量的组合；我不确定有多少组合:p|338369|在我的一个项目中，我使用了列：|338370|338371|+uIntbusiness_id，uTinyIntday，char(11)timeRange|blockquote|338372|338373|338374|如果你想支持OpeningHoursSpecification，那么你需要添加validFrom和validThrough。|338375|时间范围的格式如下:+hh:mm-hh:mm|338376|这是一个解析它的函数，如果你在数据库中将它们作为单独的列保存，你也可以修改这个函数来解析一个单一的open/close。|338377|根据我的经验，我建议您在一天内允许多次，允许一种方法来判断它们是在当天显式关闭，还是24小时或24小时全天候开放。我的建议是，如果数据库中缺少一天，那么业务就会在当天关闭。|338378|/**
+*+parseTimeRange
+*+parses+a+time+range+in+the+form+of
+*+'08:55-22:00'
+*+@param+$timeRange+'hh:mm-hh:mm'+'08:55-22:00'
+*+@return+mixed+['hourStart'=>,+'minuteStart'=>,+'hourEnd'=>,+'minuteEnd'=>]
+*/
function+parseTimeRange($timeRange)
{
++++//+no+validating+just+parsing
++++preg_match('/(?P<hourStart>\d{1,2}):(?P<minuteStart>\d{2})-(?P<hourEnd>\d{1,2}):(?P<minuteEnd>\d{2})/',+$timeRange,+$matches);

++++return+$matches;
}|code-block|syntax|javascript|338379|entityMap|0|LINK|mutability|MUTABLE|url|1|http://schema.org/OpeningHoursSpecification|2^0|0|0|V|0|0|0|17|1|0|0|1F|2|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@]|9|@$D|1I|E|1J|1|1K]]|A|$]]|$1|F|3|G|5|6|7|1L|8|@]|9|@$D|1M|E|1N|1|1O]]|A|$]]|$1|H|3|I|5|6|7|1P|8|@]|9|@$D|1Q|E|1R|1|1S]]|A|$]]|$1|J|3|K|5|6|7|1T|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1U|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1W|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|20|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|21|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|22|8|@]|9|@]|A|$]]|$1|11|3|12|5|13|7|23|8|@]|9|@]|A|$14|15]]|$1|16|3|-4|5|6|7|24|8|@]|9|@]|A|$]]]|17|$18|$5|19|1A|1B|A|$1C|C]]|1D|$5|19|1A|1B|A|$1C|1E]]|1F|$5|19|1A|1B|A|$1C|I]]]]

I have learned that if you want to have google data markup recognize your data you should follow these guidelines: 

<a href="https://schema.org/openingHours">https://schema.org/openingHours</a>

<a href="http://schema.org/OpeningHoursSpecification">http://schema.org/OpeningHoursSpecification</a> Contains "valid dates", which is very useful for some businesses.

<a href="https://schema.org/docs/search_results.html#q=hours">https://schema.org/docs/search_results.html#q=hours</a>

You should be fine without a primary key, unless you are allowing businesses to share the same hours with the join table - interestingly eventually you would have a finite amount of combinations; I'm not sure how many that would be :p

With one of my projects I used the columns:

<blockquote>
 [uInt]business_id, [uTinyInt]day, [char(11)]timeRange
</blockquote>

If you want to support OpeningHoursSpecification then you'll need to add validFrom and validThrough.

Time Range is formatted like: hh:mm-hh:mm

Here's a function that parses it, 
you can also modify this function to parse just a single open/close, if you keep them as separate columns in the DB.

Out of my experience I would recommend that you allow multiple times within a day, allow for a way to tell if they are explicitly closed on that day, or opened 24 hours or 24/7.
I had mine say that if there was a day missing in the DB then the business was closed that day.

<pre><code>/**
 * parseTimeRange
 * parses a time range in the form of
 * '08:55-22:00'
 * @param $timeRange 'hh:mm-hh:mm' '08:55-22:00'
 * @return mixed ['hourStart'=&gt;, 'minuteStart'=&gt;, 'hourEnd'=&gt;, 'minuteEnd'=&gt;]
 */
function parseTimeRange($timeRange)
{
 // no validating just parsing
 preg_match('/(?P&lt;hourStart&gt;\d{1,2}):(?P&lt;minuteStart&gt;\d{2})-(?P&lt;hourEnd&gt;\d{1,2}):(?P&lt;minuteEnd&gt;\d{2})/', $timeRange, $matches);

 return $matches;
}
</code></pre>

blocks|key|3106534|text|大多数结果对于给定的场景都很好，但如果你有多天的周期，那么它就不会那么有效，例如。8:00+AM+~+2:00+AM，那么我建议使用多周期设计。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3106535|++++{
+++++++++id:+1,
+++++++++day:+1,
+++++++++periods:+[
+++++++++++++0:+{+open:+08:00,+close:+00:00+}
+++++++++]
++++},
++++{
+++++++++id:+2,
+++++++++day:+2,
+++++++++periods:+[
+++++++++++++0:+{+open:+08:00,+close:+00:00+}
+++++++++++++1:+{+open:+00:00,+close:+02:00+}
+++++++++]
++++}|code-block|syntax|javascript|3106536|+day+：一周中的天数|offset|length|style|BOLD|3106537|如果没有句点，则表示已关闭|3106538|entityMap^0|0|0|0|5|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Most of results works fine for the given scenario, but it wont be as effective if you have periods that runs through multiple days, eg. 8:00 AM ~ 2:00 AM, then I recommend using a multi period design.
<pre><code> {
 id: 1,
 day: 1,
 periods: [
 0: { open: 08:00, close: 00:00 }
 ]
 },
 {
 id: 2,
 day: 2,
 periods: [
 0: { open: 08:00, close: 00:00 }
 1: { open: 00:00, close: 02:00 }
 ]
 }
</code></pre>
day: number of day of the week 
if no periods, means it is closed

I'm currently trying to work out the best way to store a business' hours of operation in a database.

For example:

Business A has the following hours of operation

<ul>
<li>Monday: 9am - 5pm</li>
<li>Tuesday: 9am - 5pm</li>
<li>Wednesday: 9am - 5pm</li>
<li>Thursday: 9am - 5pm</li>
<li>Friday: 9am - 5pm</li>
<li>Saturday: 9am - 12 Midday</li>
<li>Sunday: Closed</li>
</ul>

Currently I'm have a data model similar to the following

<pre><code>CREATE TABLE "business_hours" (
 "id" integer NOT NULL PRIMARY KEY,
 "day" varchar(16) NOT NULL,
 "open_time" time,
 "close_time" time
)
</code></pre>

where the "day" is restricted to a choice of the 7 days of the week in code (through the ORM). To test if a business is closed on a certain day it checks if the open_time and close_time are NULL. It is related to the business through a intermediate table (Many To Many Relationship).

Does any one have any suggestions for this database scheme? Something about it doesn't seem right to me.

Storing Business Hours in a Database

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我目前正在尝试找出在数据库中存储企业运营小时数的最佳方法。例如：业务A的运营时间如下周一上午9点至下午5点周二上午9点至下午5点周三上午9点至下午5点周四上午9点至下午5点关闭<code>H19</>周五:上午9点至12点关闭目前，我有一个类似于下面的数据模型CREATE TABLE "business_hours" ...

问在数据库中存储营业时间
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在数据库中存储营业时间EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在数据库中存储营业时间
EN