I found an interesting post on a newsgroup which made me think about a nice solution for this problem:
You have a date/interval pair stored in a table, let’s say, the 29th January and an interval of 14 days and you want to check if a date is followed by that given interval. So this should be true for 29th January (I assume that the given date should be included), 13th February, 27th February, 13th March etc.
I wanted to find a solution which can not only deal with one pair, but with many pairs. You should be able to specify a date and verify which pairs are true for the specified date (if you’re a bit confused by my explaination – my example will make it easier to understand what I mean).
I have set up an example table with several date/interval pairs:
mysql> SELECT id, dt, intvl
-> FROM date_repetitions;
+----+------------+-------+
| id | dt | intvl |
+----+------------+-------+
| 1 | 2005-09-26 | 16 |
| 2 | 2005-09-27 | 12 |
| 3 | 2005-09-30 | 18 |
| 4 | 2005-10-02 | 18 |
| 5 | 2005-10-03 | 16 |
| 6 | 2005-10-08 | 17 |
| 7 | 2005-10-12 | 16 |
| 8 | 2005-10-14 | 20 |
| 9 | 2005-10-20 | 20 |
| 10 | 2005-10-24 | 15 |
| 11 | 2005-10-25 | 8 |
| 12 | 2005-10-26 | 15 |
| 13 | 2005-10-28 | 17 |
| 14 | 2005-11-04 | 17 |
| 15 | 2005-11-07 | 12 |
| 16 | 2005-11-10 | 16 |
| 17 | 2005-11-11 | 21 |
| 18 | 2005-11-15 | 21 |
| 19 | 2005-11-19 | 10 |
| 20 | 2005-11-21 | 18 |
| 21 | 2005-11-22 | 14 |
| 22 | 2005-11-25 | 20 |
| 23 | 2005-11-29 | 11 |
| 24 | 2005-11-30 | 13 |
| 25 | 2005-12-03 | 11 |
| 26 | 2005-12-04 | 9 |
| 27 | 2005-12-06 | 20 |
| 28 | 2005-12-08 | 8 |
| 29 | 2005-12-12 | 15 |
| 30 | 2005-12-14 | 13 |
| 31 | 2005-12-15 | 16 |
| 32 | 2005-12-17 | 19 |
| 33 | 2005-12-22 | 12 |
| 34 | 2005-12-24 | 9 |
| 35 | 2005-12-27 | 13 |
| 36 | 2005-12-28 | 19 |
| 37 | 2005-12-29 | 8 |
| 38 | 2006-01-01 | 15 |
| 39 | 2006-01-02 | 15 |
| 40 | 2006-01-03 | 10 |
| 41 | 2006-01-07 | 10 |
| 42 | 2006-01-08 | 14 |
| 43 | 2006-01-14 | 20 |
| 44 | 2006-01-15 | 20 |
| 45 | 2006-01-16 | 17 |
| 46 | 2006-01-23 | 20 |
| 47 | 2006-01-24 | 12 |
| 48 | 2006-01-25 | 21 |
| 49 | 2006-01-26 | 18 |
+----+------------+-------+
49 rows in set (0.00 sec)
All we need is a *very* little Stored Procedure:
DELIMITER //
DROP PROCEDURE IF EXISTS getRepeatedDates //
CREATE PROCEDURE getRepeatedDates (pDate DATE)
BEGIN
SELECT id, dt, intvl FROM date_repetitions
WHERE (to_days(pDate) - to_days(dt)) % intvl = 0
AND to_days(pDate) >= to_days(dt);
END //
DELIMITER ;
So what does it do? It calculates an internal value of the dates (with to_days()) that starts counting at year 0 (although year zero actually never existed, because 1 B.C was followed by year 1 A.D. – that’s also, why the 3rd millenium was told to begin in 2001 and not in 2000, just as a little sidenote ;-)). So the 29th January 2006 would e.g. be transfered to 732705.
We subtract the to_days() value from the date in the database from the to_days() value from the date that’s to be checked. Than we devide the result by the interval value and get the remainder out of it. If the remainder is zero, the date meets the criteria.
The second condition in the WHERE clause just checks, if the date to be checked is greater than or equal to the date stored in the database.
So lets see, which records are OK for the 29th January 2006:
mysql> call getRepeatedDates('2006-01-29');
+----+------------+-------+
| id | dt | intvl |
+----+------------+-------+
| 11 | 2005-10-25 | 8 |
| 16 | 2005-11-10 | 16 |
| 34 | 2005-12-24 | 9 |
+----+------------+-------+
3 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)