Weekly Schedules - How can you store this in a database?

Question

Currently, I'm working on a project to manage maintenance windows on a database of servers, etc. Basically, I only need to be accurate down to the hour, but allow for them to be set to allow, or disallow, for each day of the week.

I've had a few ideas on how to do this, but since I work by myself, I'm not wanting to commit to anything without some feedback.

To visualize this, it's something like 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... 

Is there a standard way of doing this or a resource that might give me some ideas to...

1. Make a format that can be saved and restored easily
2. Make it searchable within the database (for example, not having to deserialize it to search for a time)

[Update]

It is worth mentioning that a day could, even though unlikely, be set to "allow, deny, allow, deny...etc...". The span isn't guaranteed to be the only one for the whole day.

This is also not the only schedule, there will be hundreds of devices each with their own schedule, so it's going to get hairy... lol??

Rob asked if each week needed to be tracked - It does not. This is a generic schedule that will apply to the entire year (regularly scheduled maintenance)

Answer 1

I would consider for (1) using a format that includes both start and end times, and an integer field for the day of the week. I know you stated the blocks will always be one hour, but that can be enforced by your code. Also, if your requirements change one day, you'll have a lot less to worry about in step (2) than if your DB statements are all written to assume 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's 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 represents the date and time you want to check).

The allowing or disallowing for each day of the week would be handled by separate records. IMHO, this is more flexible than hard-coding for each day of the week, since you'll then be using some kind of case statement or if-else switch to check the right DB column for the day you're interested in.

Note: Be sure you know which standard your DB uses for the integer day of the week, and try to make your code independent of it (always ask the DB). We've had lots of fun with different standards for the start of the week (Sunday or Monday) and the starting index (0 or 1).