Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

mysql - Sort upcoming birthdays based on current date

I have the following table of people and their birthdays:

name        birthday
----------------------
yannis      1979-06-29
natalia     1980-08-19
kostas      1983-10-27    
christos    1979-07-22
kosmas      1978-04-28

and I have no idea how to sort the names on how closer the birthday is to today. So for NOW() = 2011-09-08 the sorted result should be:

kostas      1983-10-27
kosmas      1978-04-28
yannis      1979-06-29
christos    1979-07-22
natalia     1980-08-19

I'm looking for a quick hack, don't really care for performance (pet project - table will hold less than 1000 records), but of course every suggestion will be extremely appreciated.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Here is one way:

  • Calculate current year - year of birth
  • Add the resulting number of years to the date of birth
  • You now have the birthday this year, if this date has passed then add one more year
  • Sort the results by that date
SELECT
    name,
    birthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday))     YEAR AS currbirthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM birthdays
ORDER BY CASE
    WHEN currbirthday >= CURRENT_DATE THEN currbirthday
    ELSE nextbirthday
END

Notes:

  • Today's birthdays appears first regardless of current time
  • February 29 birthday is treated equal to February 28 birthday for common years e.g.
    • On Jan/1/2019 both Feb 28 and Feb 29 birthdays (2019) are sorted equal
    • On Mar/1/2019 Feb 28 and Feb 29 birthdays (2020) are sorted as expected

SQLFiddle


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...