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…

Accepted Answer

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.

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

Solution

Problem

Related problems