Re: Yearly date comparison?

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