Posted by Michael Fesser on 07/22/07 01:06

..oO(Reapes)

>Thanks Michael - how would I go about it ?
>
>At the moment I am selecting all records which haven't been sent then
>*trying* to check the explained dates and intervals.

Without knowing your database design and any special requirements - just
some general ideas:

Let's assume there are some special events each year, stored with their
date in a DATE colum in the database. Even if they happen every year,
the dates should be stored including a year (could be the year when the
event did happen for the first time, like the year of birth in case of a
birthday calendar for example). Additionally there might be a simple INT
UNSIGNED column, containing the number of days that we want to get
notified before the event.

Now, to get all events that are within their notification range, you
could do something like this:

* Map the event dates to the current year. This could be done by using
DATE_ADD() to add the difference between the current year and the
event's year to the event's date, e.g.

DATE_ADD(eventDate, INTERVAL YEAR(NOW())-YEAR(eventDate) YEAR)

It's a quick 'n dirty hack, but it works (of course there might be a
better way ...)

* After you got the dates of the events in the current year, use
DATE_SUB() to substract the number of days to get the dates when the
reminding should begin.

* Now all you have to do is to select all events where the current date
NOW() is somewhere between these two calculated dates:

SELECT eventTitle
FROM events
WHERE NOW() BETWEEN eventDate AND
DATE_SUB(
DATE_ADD(
eventDate,
INTERVAL YEAR(NOW())-YEAR(eventDate) YEAR),
INTERVAL remindMe DAY
)

Just a quick shot ... untested ...

Micha

[Back to original message]