Presently, I am focusing on a task to handle maintenance home windows on the database of servers, etc. Essentially, I only have to be accurate lower towards the hour, but permit these to be set to permit, or disallow, for every day each week.

I have were built with a couple of tips on how to do that, consider Sometimes on my own, I am hesitant to invest in anything without some feedback.

To visualise this, it is something such as the flowing "graph"

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

It is possible to standard method of carrying this out or perhaps a resource that may produce ideas to...

  1. Create a format that may be saved and restored easily
  2. Allow it to be searchable inside the database (for instance, not needing to deserialize it to find a period)


It's worth mentioning that the day could, despite the fact that unlikely, be set to "allow, deny, allow, deny...etc...". The span is not certain to be the sole one for the entire day.

This is only some of the schedule, you will see 100s of products each using their own schedule, so it is going to get hairy... lol??

Take advantage of requested if every week must be monitored - It doesn't. This can be a generic schedule which will affect the season (regularly scheduled maintenance)

I'd consider for (1) utilizing a format which includes both start and finish occasions, as well as an integer area during the day each week. I understand you mentioned the blocks will be 1 hour, but that may be enforced because of your code. Also, in case your needs change eventually, you will have a lot less to bother with in step (2) than in case your DB claims are written to visualize one hour blocks.

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

For (2), if each record includes a start and finish time connected by using it, then it is easy to check on for home windows for any particular time:

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

(where $time signifies the time and date you need to check).

The permitting or disallowing for every day each week could be handled by separate records. IMHO, this really is more flexible than hard-coding for every day each week, since you may then be utilising some type of situation statement or maybe-else switch to determine the right DB column during the day you are thinking about.

Note: Make sure to know which standard your DB ways to use the integer day each week, and then try to build your code separate from it (always request the DB). We have had plenty of fun with various standards for the beginning of a few days (Sunday or Monday) and also the beginning index ( or 1).

If it will differ each week, then setup the table such as this

    StartTime DATETIME    PrimaryKey

If your start time for the date/hour is placed, then assume that it's permitted, otherwise deny.

If it's a normal configuration for any generic week that does not change, do this

    Hour  INT,
    Day   INT,
    Allow BIT

Adding rows for each hour/day combination.