Re: Date math

Adam Rich 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.
>
> The best I've come up with so far is:

<snip>

> 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?

Maybe something like this?

create table foo (f1 int, f2 timestamp);
insert into foo values (1, '07/04/1970');
insert into foo values (2, '1976-02-29');
insert into foo values (3, '1962-06-27');
insert into foo values (4, '1981-06-26');
insert into foo values (5, '1991-07-26');

create or replace function next_birthday(timestamp) returns timestamp as $$
   select case
     when now() - (extract(year from now()) - extract(year from $1))* '1
year'::interval > $1 then
       $1 + (1 + extract(year from now()) - extract(year from $1)) * '1
year'::interval
     else
       $1 + (extract(year from now()) - extract(year from $1))* '1
year'::interval
   end as next_birthday
$$ language sql;

select now()::date as right_now, f2 as real_brithday, next_birthday(f2)
from foo;
  right_now  |    real_brithday    |    next_birthday
------------+---------------------+---------------------
  2009-06-27 | 1970-07-04 00:00:00 | 2009-07-04 00:00:00
  2009-06-27 | 1976-02-29 00:00:00 | 2010-02-28 00:00:00
  2009-06-27 | 1962-06-27 00:00:00 | 2010-06-27 00:00:00
  2009-06-27 | 1981-06-26 00:00:00 | 2010-06-26 00:00:00
  2009-06-27 | 1991-07-26 00:00:00 | 2009-07-26 00:00:00
(5 rows)

Joe