Database Design Question: Availability 'calendar'

[xtopolis]

(googling this only led to premade scripts, and more focused towards appointment setting)

I am going to be storing persons' availability per day.  The times will be broken up by hour, and it will have data for each of the 7 days of the week.  I plan to use check boxes to allow the user to select which hours they are available.  This schedule is just a general idea of availability per week, and will stay that way.  I will also store their offset from GMT time since this will be international.  Concept example:

 

(Person1)

Monday:

[x] 00:00->01:00

[ ] 01:00->02:00

...etc.

[ ] 22:00->23:00

[ ] 23:00->24:00

 

Tuesday:

[ ] 00:00->01:00

[ ] 01:00->02:00

...etc.

[x] 22:00->23:00

[ ] 23:00->24:00

 

etc.

 

Idea:

-I had originally planned to use a "binary" system to store which boxes were checked... 00:00->01:00 having a value of 1, 01:00->02:00 having a value of 2, store a value of "3" to represent that they had checked those boxes.  This way, I could have a table with columns for each day of the week, storing an integer per column for a user; seems simple.

 

Concerns:

+I could later break down the times further, into half hour increments and have little effect on the table design (store a different integer)

-It seems like I'd have to convert from my "stored binary method" in PHP, and couldn't do easy selections in mysql? (for example, show all people available on Tuesday, 1pm-2pm) or (show the most popular hour per day [have to deal with timezone offset, and then conversion of stored number to hours available])

These sample queries are important to what I want to do.

 

Potential Inaccuracies:

I am not that good with binary.  If I'm not mistaken, I have the right idea above, but could be storing more data using smaller numbers (or the same #s but representing more combos)?  Please explain if I've confused myself while trying to use the concept so that I can use it more efficiently.

 

 

Specific Questions:

-Aside from what I mentioned above, I would mainly like input on a good database design to store the concept above.  How would I store their hours of availability (especially if they are not consecutive) and have a design flexible enough to break down / combine the increments differently if needed?

 

-What functions would I use in PHP or MySQL to handle conversion of timezones if I store their data in the same format, but track their GMT offset?  (This will go on a graph, localized to their time, showing the other users availability)

 

 

[Let me know if I've been unclear]

Thanks

[xtopolis]

bump