Best Way to Store Working Hours and Query It Efficiently

Best way to store working hours and query it efficiently

To store normal operation hours, you would need to store a number of records containing:

  • Shop - INTEGER
  • DayOfWeek - INTEGER (0-6)
  • OpenTime - TIME
  • CloseTime - TIME

I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records:

  • Shop - INTEGER
  • OverrideStartDate - DATE
  • OverrideEndDate - DATE
  • DayOfWeek - INTEGER (0-6)
  • AltOpenTime - TIME
  • AltCloseTime - TIME
  • Closed - INTEGER (0, 1)

To find open shops is trivial, but you also need to check if there are override hours:

SELECT Shop
FROM OverrideHours
WHERE OverrideStartDate <= NOW()
AND OverrideEndDate >= NOW()
AND DayOfWeek = WEEKDAY(NOW())

If there are any record returned, those shops have alternate hours or are closed.

There may be some nice SQL-fu you can do here, but this gives you the basics.

EDIT

I haven't tested this, but this should get you close:

SELECT Normal.Shop
FROM Normal
LEFT JOIN Override
ON Normal.Shop = Override.Shop
AND Normal.DayOfWeek = Override.DayOfWeek
AND NOW() BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate
WHERE Normal.DayOfWeek = WEEKDAY(NOW())
AND ((Override.Shop IS NULL AND TIME(NOW()) BETWEEN Normal.OpenTime AND Normal.CloseTime)
OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(NOW()) BETWEEN Override.AltOpenTime AND Override.AltCloseTime))

EDIT

As for efficiency, it is efficient in the sense that you only have to make one call to MySQL which is often a bottleneck if it is across a network. You'll have to test and see whether this performs to your specifications. If not, you may be to play with some indices.

EDIT

Testing. Not complete testing, but some.

mysql> select * from Normal;
+------+-----------+----------+-----------+
| Shop | DayOfWeek | OpenTime | CloseTime |
+------+-----------+----------+-----------+
| 1 | 1 | 09:00:00 | 17:00:00 |
| 1 | 5 | 09:00:00 | 16:00:00 |
| 2 | 1 | 09:00:00 | 17:00:00 |
| 2 | 5 | 09:00:00 | 17:00:00 |
+------+-----------+----------+-----------+
4 rows in set (0.01 sec)

mysql> select * from Override;
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
| Shop | OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime | Closed |
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
| 2 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 18:00:00 | 0 |
| 2 | 2010-12-01 | 2010-12-31 | 5 | 09:00:00 | 18:00:00 | 0 |
| 1 | 2010-12-01 | 2010-12-31 | 1 | 09:00:00 | 17:00:00 | 1 |
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
3 rows in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 16:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.01 sec)

mysql> SET @whenever = TIMESTAMP('2010-12-25 16:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime) OR (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.00 sec)

Storing Business Hours in a Database

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

  1. 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.

  2. 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.

So I guess I would do:

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
)

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.

Way to store various shop opening times in a database

Normalise your data

store it as

shop_ID, Weekday, Start_hour, end_hour

weekday can have values between 1 and 7, as an output of

SELECT DAYOFWEEK('2007-02-03')

start hour and end hour can be stored in time http://dev.mysql.com/doc/refman/5.0/en/time.html

with this you would have everything covered

To find hours on a date for a shop you would do

select start_hour, end_hour from table where weekday=dayofweek(curdate()) and shop_id=1

Need 2 time intervals for a day for a shop? no problem,

`shop ID, weekday, start_hour, end_hour`
1; 1; 08:00:00 ; 09:00:00
1; 1; 10:00:00 ; 11:00:00

For exceptions, you can add an exceptions table with the date and the shop. You can query that, and if it's null(no exception), return opening hours. Alternatively you can store every date for every shop, but that would bloat your data.

Best way to store time (hh:mm) in a database

You could store it as an integer of the number of minutes past midnight:

eg.

0 = 00:00 
60 = 01:00
252 = 04:12

You would however need to write some code to reconstitute the time, but that shouldn't be tricky.

efficient way for storing query results?

It sounds like you should be building a data warehouse.

What is the most efficient way to store and query a large group (10,000+) strings?

As I progressed, the number of Strings exceeded 1.5mil and I didn't want to store those just in a massive swift file. So i created a JSON file for it and created a singleton that parsed the data from the JSON when the app launched. This was exceedingly slow though, so I moved to the option of storing it in a SQLite database which I ship with the app. I then used the following library to search:

https://github.com/stephencelis/SQLite.swift

How to store efficiently 1B of new rows every day with a possibility to query/index?

You can try Vaex indeed!

This article give you an example of the usage, and it is using variety of common operations like filtering and selections, on a 1 billion rows in total:
https://towardsdatascience.com/how-to-analyse-100s-of-gbs-of-data-on-your-laptop-with-python-f83363dda94

There is also a guide on getting your data in, and the docs should have enough examples to get your started:
https://docs.vaex.io/en/latest/

I hope this helps!

(disclaimer: I am the author of the article above, and a core contributor to the project).



Related Topics



Leave a reply



Submit