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
