Weekly schedules - how to save this in the database?

Question

I am currently working on a project to manage maintenance windows in a server database, etc. Basically, I have to be accurate down to the hour, but let them set, allow or deny, for every day of the week.

I had a few ideas on how to do this, but since I work alone, I do not want to do anything without any feedback.

To visualize this, it looks like the current “chart”

| Sun | Mon | Tue | Wed | Thu | Fri | Sat | ------------------------------------------- 5AM |allow|allow|allow|deny |deny |allow|allow| ------------------------------------------- 6AM |allow|deny |deny |deny |deny |deny |allow| ------------------------------------------- 7AM |allow|deny |deny |deny |deny |deny |allow| ------------------------------------------- 8AM |allow|deny |deny |deny |deny |deny |allow| ------------------------------------------- 9AM |allow|deny |deny |deny |deny |deny |allow| ------------------------------------------- ... etc... 

Is there a standard way to do this or a resource that can give me some ideas ...

• Make a format that can be easily saved and restored.
• Make it searchable in the database (for example, you don't need to deserialize it to find the time)

[Update]

It should be noted that on the day it would be possible, although unlikely, to establish "allow, prohibit, allow, refuse ... etc.". The span is not guaranteed to be the only one for the whole day.

This is also not the only schedule, there will be hundreds of devices, each of which has its own schedule, so it will get hairy ... lol ??

Rob asked if you need to track every week - it is not. This is a general schedule that will be applied throughout the year (regular scheduled maintenance).

Answer 1

I would consider for (1) a format that includes both start and end times, and an entire field for the day of the week. I know that you stated that the blocks will always be for one hour, but this can be executed by your code. In addition, if your requirements change one day, you will be much less worried about step (2) than if all your database operators were recorded to receive 1-hour blocks.

 CREATE TABLE maintWindow ( maintWindowId int primary key auto_increment not null, startTime Time, endTime Time, dayOfWeek int, ... 

For (2), if each record has a start and end time associated with it, then it is very easy to check for windows for any given time:

 SELECT maintWindowId FROM maintWindow WHERE $time >= TIME(startTime) AND $time <= TIME(endTime) AND DAYOFWEEK($time) = dayOfWeek 

(where $time indicates the date and time you want to check).

Permission or prohibition for each day of the week will be processed by separate entries. IMHO, this is more flexible than hard coding for each day of the week, since then you will use some kind of case or if-else statement to check the right DB column for the day that interests you.

Note. . Make sure you know which standard your database uses for an integer day of the week, and try to make your code independent of it (always ask the database). We had a lot of fun with different standards for starting the week (Sunday or Monday) and the starting index (0 or 1).

Answer 2

If it will be different every week, then set up the table as follows:

 TABLE: StartTime DATETIME PrimaryKey 

If the start time of a specific date / hour is specified, suppose it is allowed, otherwise denial.

If this is a general configuration for a common week that does not change, try this:

 TABLE: Hour INT, Day INT, Allow BIT 

Then add lines for each combination of hours / days.

Answer 3

I really used this project before, basically creating a bitmap for the time range you want to plan regularly, divided by the number of periods you want. Therefore, in your example, you need a weekly schedule with hourly periods, so you will have a 168-bit bitmap, the length of which is only 21 bytes. A pair of datetimes is 16 bytes together, and you will need a few lines to represent possible schedules for a given week, so if you don't care about the size at all, I don't think you can beat it.

I admit that it is a little more difficult to deal with and less flexible than previous offers. Think, if you suddenly want to use half-hour periods, you will need to transcode all existing data to a new 336-bit raster map and distribute the values.

If you use SQL, you can either save this as a binary blog, or execute a bit to compare whether the bit is on or not, or you can store each bit as a column. MS SQL Server supports up to 1024 for standard or 30 thousand. For wide tables, so you can easily get a granularity of up to 10 minutes for any or much thinner for a 30k table.

Hope this adds a slightly different perspective on how this can be done. This is really only necessary if you are concerned about space / size or if you have 10 or 100 million of them.

Answer 4

You can easily just write the "allowable" times in the table. Thus, if it is not, it is prohibited. If you need to have a more variable "schedule", you can easily add the year and month field.

  TABLE DBMaintSched ID int PK ServerID varchar(30) (indexed) Day int Month char(3) DayOfWeek char(3) Year int StartDT DateTime EndDT DateTime 

In December 2008:

  SELECT * FROM DBMaintSched WHERE ServerID = 'SQLSERVER01' AND Month = 'DEC' AND Year = 2008 ORDER BY DAY ASC 

You have all the days in December 2008 on which maintenance can be carried out. Show how you want.

Answer 5

Each proposed solution is good for me, anyway, I would consider this issue if you encounter performance problems and / or table sizes. Since you would probably establish a relationship between time and your entity (i.e. the server), the size will increase by entity_number * entity_times. If you have a line for every time, this can be a pain.

This sentence is a bit uglier in terms of table structure, but more efficient when it comes to disk space and table scan speed.

 TABLE times entityFK int -- your entity foreign key day INT -- 0-7 day identifier bit time0 -- ON if the time 00:00 - 00:59 is being covered bit time1 bit time2 -- more columns bit time23 

Consider an example in which you want to assign a working time of 16:00 - 20:00 to the server on Sunday and Monday, you will only have two lines, for example

 entityFK | day | time16 | time17 | time18 | time19 | -- other bits are set to 0 server1 0 1 1 1 1 server1 1 1 1 1 1 

You assume that every missing server means a server.

If you need it, you can use the DATE format for the day column to set specific dates (for example, uptime is only 2013/10/02 between 16:00 and 20:00).

Hope this helps

Answer 6

Maybe something like

 TABLE: StartTime DATETIME PrimaryKey, EndTime DATETIME PrimaryKey, /*if you are positive it will be in one hour incerments then you might want to omit this one*/ Monday BIT, TuesDay BIT, Wednesday BIT, Thursday BIT, Friday BIT, Saturday BIT, Sunday BIT 

Answer 7

Written in Python, such a module can work https://github.com/AndrewPashkin/pytempo