Le mardi 28 février 2012 à 20:14 -0800, Nick a écrit :
> What is the best way to find an event with a yearly occurrence?
>
>
> start_date DATE,
> end_date DATE,
> recurring TEXT
> );
Hi Nick,
Your problem seems similar to that of managing subscriptions?
If you can do anything about it, you might make things simpler with a
table structure like this:
CREATE TABLE events (
last_date DATE,
duration integer,
recurring integer)
where last_date is the date when the event was held last time,
duration and recurring are a number of units (chosen as appropriate :
hours, days, weeks, months, years...)
> INSERT INTO events (start_date, end_date, recurring) VALUES
> ('2010-02-28','2010-03-01','yearly');
>
Using days as the unit, this becomes
INSERT INTO events (last_date, duration, recurring) VALUES
('2010-02-28', 3, 365);
You then run daily:
SELECT * FROM events where (last_date + recurring) <= NOW();
For all records that show up :
-start event
-update db with : UPDATE events SET last_date=NOW() WHERE ...
you may want to add a field initial_date that stays untouched, if you
want to record when the event was held first
> Since I may not know how many years back the start/end_date is, is
> there a way to just ignore the year or make it the current year,
> without killing performance?
>
With the structure you have now, you'll have to refactor your code (or
add a function that does it for you) every year.
--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique
