Query upcoming birthdays with php mysql-CodePudding

I have table:

 ------------------- 
| name | birthday   |
|-------------------|
| A    | 1972-04-24 |
| B    | 1994-12-27 |
| C    | 1974-11-11 |
| D    | 2003-07-23 |
| E    | 1968-01-06 |
| F    | 1977-01-28 |
| G    | 1986-06-06 |
 ------|------------

I want to query for the results of the next 3 birthdays from today regardless of the year (LIMIT 3). I use

SELECT * FROM table WHERE DAYOFYEAR(DATE_ADD(birthday, INTERVAL (YEAR(NOW()) - YEAR(birthday)) YEAR)) > $doy_today ORDER BY DAYOFYEAR(birthday) ASC LIMIT 3

($doy_today is day-of-year today)

It seems to works fine except for some days end of the year. For example today is 2021-12-25, the result is:

1/ 12-27 B

instead of

1/ 12-27 B
2/ 01-06 E
3/ 01-28 F

I know that dayofyear of Dec-31 is the greatest so there won't be a bigger dayofyear. How can I fix it, or someone show me another way Thank you very much

CodePudding user response：

I would phrase this by taking the difference in days between today and the birthday. Then, use the modulus 365 to figure out which 3 people have the nearest birthday.

SELECT *
FROM yourTable
ORDER BY DATEDIFF(NOW(), TIMESTAMPADD(YEAR, YEAR(NOW()) - YEAR(birthday) - 1, birthday) % 365
LIMIT 3;

CodePudding user response：

Someone's next birthday on or after today (treating people who have a Feb 29th birthday as having the 28th in a non-leap year) is:

case
    when birthday   interval year(current_date) - year(birthday) year < current_date then
       birthday   interval year(current_date)   1 - year(birthday) year
    else
       birthday   interval year(current_date) - year(birthday) year
end

You just need to select that and order by it; trying to order by dayofyear is not going to be helpful.