From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Nick *EXTERN*" <nboutelier(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Yearly date comparison? |
Date: | 2012-02-29 08:44:58 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C2078D8571@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2012-02-29 08:52:00 | Re: Correct way for locking a row for long time without blocking another transactions (=nowait)? |
Previous Message | Chris Angelico | 2012-02-29 08:32:49 | Re: what Linux to run |