Retrieve upcoming birthdays in Postgres

Question

I have a users table with a dob (date of birth) field, in a postgres database.

I want to write a query that will retrieve the next five upcoming birthdays. I think the following needs to be considered -

Sorting by date of birth won't work because the years can be different.
You want the result to be sorted by date/month, but starting from today. So, for example, yesterday's date would be the last row.
Ideally, I would like to do this without functions. Not a deal breaker though.

Similar questions have been asked on SO, but most don't even have an accepted answer. Thank you.

if it is a upcoming birthday, why should yesterday's row be included? — Vamsi Prabhala, Sep 11 '17 at 20:28
@VamsiPrabhala - I was just trying to make the point about how sorting should work. — Anuj Jain, Sep 12 '17 at 07:51

score 3 · Accepted Answer · answered Sep 12 '17 at 14:29

Well, this got downvoted a lot. But I'll post my answer anyway. One of the answers helped me arrive at the final solution, and that answer has been deleted by its owner for some reason.

Anyway, this query works perfectly. It gives the next 5 upcoming birthdays, along with the dates.

SELECT id, name,
  CASE
    WHEN dob2 < current_date THEN dob2 + interval '1 year'
    ELSE dob2
  END 
  AS birthday
FROM people, 
make_date(extract(year from current_date)::int, extract(month from dob)::int, extract(day from dob)::int) as dob2
WHERE is_active = true
ORDER BY birthday
LIMIT 5;

I think this will break in non leap years, if someone has birthday on February 29th. — bpieck, Jun 03 '19 at 14:39

score 1 · Answer 2 · answered Sep 11 '17 at 20:45

You can look at day of year of dob and compare against current date's doy:

SELECT doy
    , extract(doy from dob) - extract(doy from current_date) as upcoming_bday
FROM users
WHERE extract(doy from dob) - extract(doy from current_date) >= 0
order by 2 
limit 5

Retrieve upcoming birthdays in Postgres

2 Answers2