Re: Fwd: Date math

Guy Flaherty wrote:
> On Sun, Jun 28, 2009 at 1:52 PM, Adam Rich <adam.r@sbcglobal.net
> <mailto:adam.r@sbcglobal.net>> 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.