Date math

From: Adam Rich <adam(dot)r(at)sbcglobal(dot)net>
To: postgres general <pgsql-general(at)postgresql(dot)org>
Subject: Date math
Date: 2009-06-28 03:52:22
Message-ID: 4A46E8F6.2080508@sbcglobal.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua Tolley 2009-06-28 04:06:52 Re: Free OLAP software for Postgres databas
Previous Message Greg Stark 2009-06-28 03:02:51 Re: Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function