Skip site navigation (1) Skip section navigation (2)

Re: Date math

From: Joe Conway <mail(at)joeconway(dot)com>
To: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>
Cc: postgres general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Date math
Date: 2009-06-28 04:52:30
Message-ID: 4A46F70E.5070508@joeconway.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group