SQL Infinite Calendar Pattern

I have built this kind of calendar before. I found the best way to do it is to approach it the way that crons are scheduled. So in the database, make a field for minute, hour, day of month, month, and day of week.

For an event every Friday in June and August at 10:00pm your entry would look like

Minute  Hour  DayOfMonth  Month  DayOfWeek 0       22     *          6,8       5

You could then have a field that flags it as a one time event which will ignore this information and just use the start date and duration. For events that repeat that end eventually (say every weekend for 3 months) you just need to add an end date field.

This will allow you to select it back easily and reduce the amount of data that needs to be stored. It simplifies your queries as well.

I don't think there is a need to create temporary tables. To select back the relevant events you would select them by the calendar view. If your calendar view is by the month, your select would look something like:

SELECT Events.* FROM Events WHERE (Month LIKE '%,'.$current_month.',%' OR Month = '*')     AND DATE(StartDate) >= "'.date('Y-m-d', $firstDayOfCurrentMonth).'"     AND DATE(EndDate) <= "'.date('Y-m-d', $lastDayOfCurrentMonth).'"

Obviously this should be in a prepared statement. It also assumes that you have a comma before and after the first and last value in the comma separated list of months (ie. ,2,4,6,). You could also create a Month table and a join table between the two if you would like. The rest can be parsed out by php when rendering your calendar.

If you show a weekly view of your calendar you could select in this way:

SELECT Events.* FROM Events WHERE (DayOfMonth IN ('.implode(',', $days_this_week).','*')     AND (Month LIKE '%,'.$current_month.',%' OR Month = '*'))     AND DATE(StartDate) >= "'.date('Y-m-d', $firstDayOfCurrentMonth).'"     AND DATE(EndDate) <= "'.date('Y-m-d', $lastDayOfCurrentMonth).'"

I haven't tested those queries so there maybe some messed up brackets or something. But that would be the general idea.

So you could either run a select for each day that you are displaying or you could select back everything for the view (month, week, etc) and loop over the events for each day.

I like Veger's solution best .. instead of populating multiple rows you can just populate the pattern. I suggest the crontab format .. it works so well anyway.

You can query all patterns for a given customer when they load the calendar and fill in events based on the pattern. Unless you have like thousands of patterns for a single user this should not be all that slow. It should also be faster than storing a large number of row events for long periods. You will have to select all patterns at once and do some preprocessing but once again, how many patterns do you expect per user? Even 1000 or so should be pretty fast.

I've had this idea since I was still programming in GW Basic ;-) though, back then, I took option #3 and that was it. Looking back at it, and also some of the other responses, this would be my current solution.

table structure

start (datetime)stop (datetime, nullable)interval_unit ([hour, day, week, month, year?])interval_every (1 = every <unit>, 2 every two <units>, etc.)type ([positive (default), negative]) - will explain later

Optional fields:

titleduration

The type field determines how the event is treated:

1. positive; normal treatment, it shows up in the calendar
2. negative; this event cancels out another (e.g. every Monday but not on the 14th)

helper query

This query will narrow down the events to show:

SELECT * FROM `events`WHERE `start` >= :start AND (`stop` IS NULL OR `stop` < :stop)

Assuming you query a range by dates alone (no time component), the the value of :stop should be one day ahead of your range.

Now for the various events you wish to handle.

single event

start = '2012-06-15 09:00:00'stop = '2012-06-15 09:00:00'type = 'positive'

Event occurs once on 2012-06-15 at 9am

bounded repeating event

start = '2012-06-15 05:00:00'interval_unit = 'day'interval_every = 1stop = '2012-06-22 05:00:00'type = 'positive'

Events occur every day at 5am, starting on 2012-06-15; last event is on the 22nd

unbounded repeating event

start = '2012-06-15 13:00:00'interval_unit = 'week'interval_every = 2stop = nulltype = 'positive'

Events occur every two weeks at 1pm, starting on 2012-06-15

repeating event with exceptions

start = '2012-06-15 16:00:00'interval_unit = 'week'interval_every = 1type = 'positive'stop = nullstart = '2012-06-22 16:00:00'type = 'negative'stop = '2012-06-22 16:00:00'

Events occur every week at 4pm, starting on 2012-06-22; but not on the 22nd