开放时间的SQL[英] SQL for Opening Hours

本文是小编为大家收集整理的关于开放时间的SQL的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

在我的商店数据库中,我需要有开放时间.您有一个想法如何在DB中实施吗?

开放时间是从星期一到周日,每天可以有2个开放式窗户(ex 09:00-12:00,16:00-19:00)

推荐答案

构建另一个表格,称其为schedules,将外键添加到shops表主键,a Day of week字段,time_open,time_closed. 数据应该看起来像这样:

shop_id     day_of_week      time_open        time_closed
1           1                09:00            12:00
1           1                16:00            19:00
1           2                09:00            12:00
1           2                16:00            19:00
1           3                09:00            12:00
1           3                16:00            19:00
1           6                10:00            14:00
2           1                09:00            12:00
2           1                13:00            18:00

这将使您有机会使用任意数量的窗口以及需要多少个例外的时间来构建任何类型的时间表.它是普遍的,仅限于它期望一周相同的事实.没有考虑假期,也没有某人可能会使用的奇数时间表.

编辑:
有了朱利安(Julien)的问题,关于夜间业务的工作时间,我注意到以前的解决方案并不是最好的BU.您不能在20:00打开栏,在06:00关闭,并比较当前时间(02:45)是否在此间隔内,因为它不会.这就是为什么,在方便的度量单位(例如)中,不要注册关闭时间,而是总工作时间(例如).

是最方便的.

shop_id     day_of_week      time_open        working_time
1           1                09:00            180
1           1                16:00            180
1           2                09:00            180
1           2                16:00            180
1           3                09:00            180
1           3                16:00            180
1           6                10:00            240
2           1                09:00            180
2           1                13:00            300

其他推荐答案

您正在使用哪种类型的数据库?如果是mysql,请放入"时间"类型的字段.然后,您可以将它传递到时间,并对它们进行基本的SQL时间操作.整个帖子都假设MySQL.

如果只有一家商店,并且您知道它永远不会生长,只需制作一个名为"时间"的桌子,您将商店打开的时间.然后,如果您想查看商店是否打开,只需检查当前时间与数据库中的一个范围.

如果有多个商店,请制作一个名为" Times"的表格,并称为"商店"表.在时代表中,可以使用称为" store_id"的ID"打开"和"关闭"列.商店表也需要一个ID.然后,您可以说:

"SELECT open, close FROM times WHERE store_id=x"

这将为您提供与特定商店相关的所有时间范围.

本文地址:https://www.itbaoku.cn/post/597464.html

问题描述

In my shops database I need to have the opening hours. Do you have an idea how i can implement this in my dB?

The opening hours are from Monday to Sunday, each day can have 2 opening windows (ex 09:00-12:00, 16:00-19:00)

推荐答案

build another table, call it schedules, add a foreign key to the shops table primary key, a Day of week field, time_open, time_closed. The data should look something like this:

shop_id     day_of_week      time_open        time_closed
1           1                09:00            12:00
1           1                16:00            19:00
1           2                09:00            12:00
1           2                16:00            19:00
1           3                09:00            12:00
1           3                16:00            19:00
1           6                10:00            14:00
2           1                09:00            12:00
2           1                13:00            18:00

This will give you the opportunity to build any kind of schedules, with as many windows as you want, with how many exceptions you need. It's universal, limited only to the fact that it expects all weeks to be identical. No holidays considered, nor odd/even-week schedules that someone might use.

Edit:
With Julien's question, about working hours of a night business, it has come to my attention that the previous solution is not the best bu far. You can't have a bar open at 20:00, close at 06:00, and compare if current time (02:45) is inside this interval, because it won't be. That's why, it would be most convenient to register not the closing time, but the total working time, in the convenient unit of measure (minutes for example).

shop_id     day_of_week      time_open        working_time
1           1                09:00            180
1           1                16:00            180
1           2                09:00            180
1           2                16:00            180
1           3                09:00            180
1           3                16:00            180
1           6                10:00            240
2           1                09:00            180
2           1                13:00            300

其他推荐答案

What type of database are you using? If it's mysql, put in a field of type "time". Then you can pass it the times and do basic sql time manipulations on them. This whole post assumes mysql.

If there is only one store and you know it will never grow, just make a table called times where you keep the times the store is open. Then, if you want to see if the store is open just check the current time against one of the ranges in the database.

If there is more than one store, make a table called "times" and a table called "stores". In the times table, columns can be "open" and "close" with an id called "store_id". The stores table needs an id too. Then, you can just say:

"SELECT open, close FROM times WHERE store_id=x"

This will give you ALL the time ranges associated with the particular store.