From: | Adam Rich <adam(dot)r(at)sbcglobal(dot)net> |
---|---|
To: | postgres general <pgsql-general(at)postgresql(dot)org> |
Subject: | Date math |
Date: | 2009-06-28 03:52:22 |
Message-ID: | 4A46E8F6.2080508@sbcglobal.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have a table with a DATE field "birth_date". The data obviously
contains various dates in the past, such as 07/04/1970. In my query, I
need to retrieve the person's "next" birthday. In other words, for the
example date 07/04/1970, the query should return 07/04/2009 for the
current week, but after this July 4th, it would return 07/04/2010.
Ultimately, I need to find people with "next" birthdays within a certain
range.
The best I've come up with so far is:
select case
when to_char(birth_date, 'MMDD') <= to_char(current_date, 'MMDD') then
(to_char(birth_date, 'MM/DD/')||date_part('year', now())+1)::date
else (to_char(birth_date, 'MM/DD/')||to_char(current_date,'YYYY'))::date
end as next_birthday
from people inner join openings on people.id=openings.id
where case
when to_char(birth_date, 'MMDD') <= to_char(current_date, 'MMDD') then
(to_char(birth_date, 'MM/DD/')||date_part('year', now())+1)::date
else (to_char(birth_date, 'MM/DD/')||to_char(current_date,'YYYY'))::date
end between openings.item_date - interval '1 month'
and openings.item_date + interval '1 month'
This seems to work for most cases, but fails for Feb 29 birthdates. And
converting dates to strings and back again seems like a hack... Is there
a better way? (I prefer to treat 02/29 as 03/01 for non-leap years)
Is there a way to add just enough years to birth_date to bring the
result into the future?
Adam
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Tolley | 2009-06-28 04:06:52 | Re: Free OLAP software for Postgres databas |
Previous Message | Greg Stark | 2009-06-28 03:02:51 | Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function |