PHP/MySQL: Model repeating events in a database but query for date ranges

Question

I'm working on a (what I was intending to be) simple PHP/MySQL app. As part of it I'd like to be able to model repeating events, however I need to be able to query all the events that happened between two dates (including repeated events). The events only have a date, the time of day doesn't matter.

I've been researching this and have looked in to various approaches including Calendar Recurring/Repeating Events - Best Storage Method and Repeating calendar events and some final maths.

However, any example of a database schema supporting this that I find online, only seems to support querying for events that happened on a certain day. There is no support for events that happened between a range of dates.

As an abstract example

Events table (with some sort of repeat representation):

Event   | Start Date   |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days

Target result of abstract query SELECT Events BETWEEN 09/Dec/2012 AND 20/Dec/2012

Event   |  Date        |   Repeats
-------------------------------------
Meeting | 10/Dec/2012  |   Every 7 days
Meeting | 17/Dec/2012  |   Every 7 days
Lunch   | 10/Dec/2012  |   Every 1 days
Lunch   | 11/Dec/2012  |   Every 1 days
Lunch   | 12/Dec/2012  |   Every 1 days
Lunch   | 13/Dec/2012  |   Every 1 days
etc...
Lunch   | 20/Dec/2012  |   Every 1 days

Is there a database schema that will support these kind of queries? How would I go around making a query on that schema for any event (including repeating events) that happened between two days?

Or perhaps a design pattern that is used for repeating events?

Answer 1

I would create a tally table with just one col called id and fill that table with numbers from 0 to 500. Now we easily use that to make selections instead of using a while loop.

Id
-------------------------------------
0
1
2
etc...

Then i'd store the events in a table with Name as varchar, startdate as datetime and repeats as int

Name    | StartDate            |   Repeats
-------------------------------------
Meeting | 2012-12-10 00:00:00  |   7
Lunch   | 2012-12-10 00:00:00  |   1

Now we can use the tally table to select all dates between two dates by using:

SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
FROM `tally`
WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
ORDER BY Id ASC

ShowDate
-------------------------------------
2012-12-09 00:00:00
2012-12-10 00:00:00
2012-12-11 00:00:00
2012-12-12 00:00:00
2012-12-13 00:00:00
2012-12-14 00:00:00
2012-12-15 00:00:00
2012-12-16 00:00:00
2012-12-17 00:00:00
2012-12-18 00:00:00
2012-12-19 00:00:00
2012-12-20 00:00:00

Then we join this on the events table to calculate the difference between the startdate and the showdate. We devided the results of this by the repeats column and if the remainder is 0, we have match.

All combined becomes:

SELECT E.Id, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff
FROM events AS E, (
    SELECT DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY) as showdate
    FROM `tally`
    WHERE (DATE_ADD('2012-12-09 00:00:00',INTERVAL Id DAY)<='2012-12-20 00:00:00')
    ORDER BY Id ASC
) a
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0
AND A.ShowDate>=E.StartDate

Which results in

Id  | Name       |StartDate             | Repeats   | ShowDate              | diff
---------------------------------------------------------------------------------
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-10 00:00:00   | 0
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-11 00:00:00   | -1
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-12 00:00:00   | -2
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-13 00:00:00   | -3
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-14 00:00:00   | -4
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-15 00:00:00   | -5
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-16 00:00:00   | -6
1   | Meeting    | 2012-12-10 00:00:00  | 7         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-17 00:00:00   | -7
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-18 00:00:00   | -8
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-19 00:00:00   | -9
2   | Lunch      | 2012-12-10 00:00:00  | 1         | 2012-12-20 00:00:00   | -10

Now you could (and should!) speed things up. For instance by directly storing dates in a table so you can just select all dates directly instead of using a tally table with dateadd. Every thing you can cache and dont have to calculate again is good.