Обсуждение: Date math

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

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.

Adam Rich wrote:
 > Guy Flaherty wrote:
 >>
 >>
 >> 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.

it kind of ugly looking but here is one that uses only math and no
problem with leap years or anything

select current_date,  '07-02-1979'::date +
    ((date_part('year',current_date) - date_part( 'year',
'07-02-1979'::date))::text||'year')::interval

So the select statement might look like this

select birth_day
      where  birthday  + ((date_part('year',current_date) - date_part(
'year',birth_day))::text||'year')::interval Between now() and now() +
'90 day'::interval

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