## Re: Date math

From: Joe Conway Adam Rich postgres general Re: Date math 2009-06-28 04:52:30 4A46F70E.5070508@joeconway.com (view raw or whole thread) 2009-06-28 03:52:22 from Adam Rich  2009-06-28 04:13:52 from Guy Flaherty   2009-06-28 04:27:33 from Adam Rich    2009-06-28 04:39:08 from Justin  2009-06-28 04:52:30 from Joe Conway   2009-06-28 06:21:25 from Guy Flaherty pgsql-general
```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

```

### In response to

• Date math at 2009-06-28 03:52:22 from Adam Rich

### pgsql-general by date

 Next: From: Greg Smith Date: 2009-06-28 05:59:56 Subject: Re: horizontal sharding Previous: From: Justin Date: 2009-06-28 04:39:08 Subject: Re: Fwd: Date math