Date math
От | Adam Rich |
---|---|
Тема | Date math |
Дата | |
Msg-id | 4A46E8F6.2080508@sbcglobal.net обсуждение исходный текст |
Ответы |
Re: Date math
|
Список | 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
В списке pgsql-general по дате отправления: