Обсуждение: Yearly date comparison?

What is the best way to find an event with a yearly occurrence?

CREATE TABLE events (
    start_date DATE,
    end_date DATE,
    recurring TEXT
);
INSERT INTO events (start_date, end_date, recurring) VALUES
('2010-02-28','2010-03-01','yearly');

SELECT * FROM events WHERE (start_date+'2 YEARS'::INTERVAL) >= NOW()
AND (end_date+'2 YEARS'::INTERVAL) < NOW();

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?

Nick wrote:
> What is the best way to find an event with a yearly occurrence?
>
> CREATE TABLE events (
>     start_date DATE,
>     end_date DATE,
>     recurring TEXT
> );
> INSERT INTO events (start_date, end_date, recurring) VALUES
> ('2010-02-28','2010-03-01','yearly');
>
> SELECT * FROM events WHERE (start_date+'2 YEARS'::INTERVAL) >= NOW()
> AND (end_date+'2 YEARS'::INTERVAL) < NOW();
>
> 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?

I guess that you mixed up < and > in your sample query.

What about

WITH n AS
   (SELECT EXTRACT(DAY FROM current_timestamp)
         + 100*EXTRACT(MONTH FROM current_timestamp) AS d)
SELECT events.*
FROM events CROSS JOIN n
WHERE EXTRACT(DAY FROM start_date)
    + 100*EXTRACT(MONTH FROM start_date) <= n.d
  AND EXTRACT(DAY FROM end_date)
    + 100*EXTRACT(MONTH FROM end_date) > n.d;

If you define an SQL function for
EXTRACT(DAY FROM dat) + 100*EXTRACT(MONTH FROM dat)
it will look much nicer.

Yours,
Laurenz Albe

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