How to calculate next birthday given a date of birth?

Question

Given this schema in a Postgres database:

CREATE TABLE person (
    id serial PRIMARY KEY,
    name text,
    birth_date date,
);

How would I query the table to get the date of each person's next birthday after today?

For example if Bob's birth_date is 2000-06-01 then his next birthday would be 2016-06-01.

Note: I'm not looking for the birth_date + a pre-defined interval but rather the next anniversary of a person's birth.

I've written the equivalent in Python:

def next_birthday(self):
    today = datetime.date.today()
    next_birthday = self.birth_date.replace(year=today.year)
    if next_birthday < today:
        next_birthday = next_birthday.replace(year=today.year + 1)
    return next_birthday

However I'd like to see if Postgres can do this in a more performant way.

@PetrHejda – that answer selects birthdays in a given date range (1 week from today). I'm looking to get the next anniversary of each person's birthday. — ghickman, May 06 '16 at 21:09
@Shanimal – that answer filters rows, whereas I'm looking to calculate the next upcoming birthday for each person. — ghickman, May 06 '16 at 21:10
What about Feb 29, what should it return? If there is no Feb 29 next year, should it return Feb 28, Mar 1, or the next Feb 29? — Peter, May 07 '16 at 08:04

score 8 · Accepted Answer · answered May 07 '16 at 07:29

select birth_date,
       cast(birth_date + ((extract(year from age(birth_date)) + 1) * interval '1' year) as date) as next_birthday
from person
where name = 'Bob'

The expression (extract(year from age(birth_date)) + 1) * interval '1' year calculates the age at the next birthday in (complete) years. When adding that to the date of birth, this gives the next birthday.

The cast is necessary to get a real date back, because date + interval returns a timestamp (including a time).

If you remove the where condition, you'll get all "next" birthdays.

You can also get a list of the upcoming birthdays in e.g. the next 30 days using something like this:

select next_birthday,
       next_birthday - current_date as days_until_next
from (
  select birth_date,
         cast(birth_date + ((extract(year from age(birth_date)) + 1) * interval '1' year) as date) as next_birthday
  from person
) as upcoming
where upcoming.next_birthday <= current_date + 30
order by next_birthday;

Brilliant :) How would you extend/change this query so it would not only give you a list of the next birthdays, but also the birthdays in 2, 3, 4.. years? In this case i would do `LIMIT 50`.. thanks in advance! — James Cazzetta, Sep 13 '16 at 12:15
The second query is the best solution for the upcoming birthday problem I found so far. The only issue that it has: it does not capture todays birthdays, but only the ones from tomorrow on. — yglodt, Mar 08 '21 at 21:37

score 0 · Answer 2 · answered May 07 '16 at 01:49

SELECT
    birth_date
    +
    cast(
        date_part('year', current_date)
        -
        date_part('year', birth_date)
        +
        CASE
            WHEN
                date_part('month', birth_date) <
                        date_part('month', current_date)
                OR (
                    date_part('month', birth_date) =
                            date_part('month', current_date)
                    AND
                    date_part('day', birth_date) <
                            date_part('day', current_date)
                )
            THEN 1
            ELSE 0
        END || ' year' as interval)
FROM person;

score -1 · Answer 3 · answered May 06 '16 at 20:58

-1

SELECT date birth_date + interval '1 year' as next_birthday from person;

answered May 06 '16 at 20:58

somesingsomsing

3,182
4
29
46

This only gives the person's first birthday but I realise my question is ambiguous on that point and will update! – ghickman May 06 '16 at 21:07

How to calculate next birthday given a date of birth?

3 Answers3

Linked