Guy Flaherty wrote:
On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <adam.r@xxxxxxxxxxxxx
<mailto:adam.r@xxxxxxxxxxxxx>> wrote:
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.
You could use the extract() function to calculate the day of year of the
person's birthdate and then check if this number is within today's day
of year and range of days you want to check for, for example, today's
day of year + 30 days to be within a month. That way you don't need to
worry about years at all. You may need to double check this will work on
the leap years though!
Thanks! that's even better than what I just came up with:
birth_date + ((interval '1 year') * ceil(EXTRACT(DAYS FROM (now() -
birth_date))/365.25))
And I like the "Day of year" solution because (I think) I can use a
functional index on that value.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
