Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 423
yannis Avatar asked Sep 08 '11 06:09

yannis


3 Answers

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

like image 198
Salman A Avatar answered Nov 04 '22 08:11

Salman A


SELECT name
     , birthday
FROM TableX
ORDER BY DAYOFYEAR(birthday) < DAYOFYEAR(CURDATE())
       , DAYOFYEAR(birthday)

No, the above may produce error results, due to years with 366 days. This is correct:

SELECT name
     , birthday
FROM
  ( SELECT name
         , birthday
         , MONTH(birthday) AS m
         , DAY(birthday) As d
    FROM TableX
  ) AS tmp
ORDER BY (m,d) < ( MONTH(CURDATE()), DAY(CURDATE()) )
       , m
       , d

If your table grows to more than a few thousands records, it will be real slow. If you want a fast query, add fields with the month and day and have an index on (bmonth,bday) or add them as one field, either Char (08-17 or 0817 for 17-Aug) or Int (817 for 17-Aug) and an index on that field.

like image 5
ypercubeᵀᴹ Avatar answered Nov 04 '22 07:11

ypercubeᵀᴹ


Seems to be rather fast, no problems with leap years:

SELECT * 
FROM `people` 
ORDER BY CONCAT(SUBSTR(`birthday`,6) < SUBSTR(CURDATE(),6), SUBSTR(`birthday`,6))

Все гениальное -- просто! ;)

like image 4
Yuri P. Avatar answered Nov 04 '22 08:11

Yuri P.