CODEWITHSUNDEEP
phpmysql
bin2003
bin2003

Reputation: 25

Query upcoming birthdays with php mysql

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

Upvotes: 0

Views: 112

Answers (2)

ysth
ysth

Reputation: 98398

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.

select
    name,
    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 next_birthday
from `table`
order by next_birthday, name
limit 3

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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;

Upvotes: 0

Related Questions