Extract birthdays in natural order

Question

Given the folowing mysql table:

ID|name|year|month|day
----------------------
1 |john|1978|5|1  
2 |mike|1979|7|23  
3 |bob |1985|2|14  
4 |joe |1964|2|16  
5 |jane|1975|9|22

I'm trying to extract the users in order of their birthday events, upcoming birthdays first. So, if the query is executed on September 16, the order should be: jane, bob, joe, john, mike.

Answer 1

so an index would have only involving a single field

ID|name|year|month|day|orderextrac
----------------------
1 |john|1978|5|1  |19780501
2 |mike|1979|7|23 |19790723 
3 |bob |1985|2|14 |19850214
4 |joe |1964|2|16 |19640216
5 |jane|1975|9|22 |19750922

select name from table1 order orderextrac

Answer 2

If you stored the birthday's as a date object, you could use this:

SELECT * 
FROM BirthdayTable
ORDER BY dayofyear(birthdayDateColumn - INTERVAL dayofyear(now()) -1 DAY)

Or, with the date fields separate, I think you could use something like this:

SELECT * 
FROM BirthdayTable
ORDER BY dayofyear(cast(CONCAT_WS("-", year, month, day) as date) - INTERVAL dayofyear(now()) -1 DAY)

These ORDER BY statements sort things based on the birthdate's day in the year, offset by the current day of the year, to get the ordering starting today for each birthday.

Answer 3

SELECT  u.*, CAST(CONCAT_WS('.', YEAR(SYSDATE()) + (CAST(CONCAT_WS('.', YEAR(SYSDATE()), month, day) AS DATE) < SYSDATE()), month, day) AS DATE) AS nbd
FROM    t_users u
ORDER BY
        nbd;

The ORDER BY expression takes the boolean result of the comparison of the current year's birthday to the current date and adds it to the current year.

This results in the next birthday (expressed as DATE) on which the statement can be ordered by.

As a bonus, you can easily get this next birthday:

5, 'jane',  1975, 9, 22, '2009-09-22'
3, 'bob',   1985, 2, 14, '2010-02-14'
4, 'joe',   1964, 2, 16, '2010-02-16'
1, 'john',  1978, 5, 1,  '2010-05-01'
2, 'mike',  1979, 7, 23, '2010-07-23'

Update:

This query handles leap years better.

SELECT  u.*, CAST(CONCAT_WS('.', year, month, day) AS DATE),
        CAST(CONCAT_WS('.', 1980, month, day) AS DATE) + INTERVAL YEAR(SYSDATE()) - 1980 YEAR +
        INTERVAL CAST(CONCAT_WS('.', 1980, month, day) AS DATE) + INTERVAL YEAR(SYSDATE()) - 1980 YEAR < SYSDATE() YEAR AS nbd
FROM    t_users u
ORDER BY
        nbd;

It assumes that the non leap-year birthday for a person born on Feb 29 is Feb 28.

I added a person named Alex who was born on Feb 29, 1980:

5, 'jane', 1975, 9, 22, '1975-09-22', '2009-09-22'
3, 'bob',  1985, 2, 14, '1985-02-14', '2010-02-14'
4, 'joe',  1964, 2, 16, '1964-02-16', '2010-02-16'
6, 'alex', 1980, 2, 29, '1980-02-29', '2010-02-28'
1, 'john', 1978, 5, 1,  '1978-05-01', '2010-05-01'
2, 'mike', 1979, 7, 23, '1979-07-23', '2010-07-23'

Answer 4

I would have stored the birth dates in a date format, but baring that, try to do something like this - select all the people with theit birth dates converted to the date of their next birthday, and then order on that. Creating a date from arbitrary strings is easy in MySQL using STR_TO_DATE. Then you only need to do an IF statement to select the correct year. Try something like this:

SELECT * FROM (
    SELECT name, IF(
        STR_TO_DATE(CONCAT(YEAR(NOW()), '-', month, '-', day)) < NOW(),
        STR_TO_DATE(CONCAT(YEAR(NOW())+1, '-', month, '-', day)), 
        STR_TO_DATE(CONCAT(YEAR(NOW()), '-', month, '-', day))) AS next_birthday 
    FROM people) as next_birthdays 
 ORDER BY next_birthday;

That I think should do the trick. Of course it would have been easier if you'd have a single DATE type birthday column.

Answer 5

SELECT *
  FROM table
 ORDER BY CASE WHEN month*100+day > MONTH(getdate())*100+DAY(getdate())
           THEN (month - MONTH(getdate()))*100 + day - DAY(getdate())
           ELSE (12 + month - MONTH(getdate()))*100 + day - DAY(getdate())
      END

Answer 6

Here's one way

select *
from foo 
order by 
if ( (dayofyear(concat(year(now()),'-',month,'-',day))-dayofyear(now())) < 0,
     (dayofyear(concat(year(now()),'-',month,'-',day))-dayofyear(now()))+365,
     (dayofyear(concat(year(now()),'-',month,'-',day))-dayofyear(now()))
   );

Basically, it orders by the number of days until the birthday this year, unless that is in the past, which case it adds another 365 days to it to find out how many days until it next comes around.

Answer 7

The most elegant solution is to ignore years altogether. There is no need to convert anything to a date type to perform the sorting.

Instead, create a sorting key that assumes all months are 31 days (the max). When the month is in the future, add month * 31 days. When in the past, add that plus a year. Then add an offset for the day.

For the current month, look at the day of the month, and do the same thing: if in the future, add it. If not, add it plus 12 "long" (31-day) months.

This solution will work properly in leap years, and requires no conversion of the individual fields to dates.

 SELECT * FROM mytable ORDER BY
     CASE 
       /* Month has passed this year, sort key considers it a "long year" further in the future) */
       WHEN month - MONTH(NOW()) < 0 THEN (month+12) * 31 + day
       /* Month has not passed year, sort key is a "long month" + days in the future */
       WHEN month - MONTH(NOW()) > 0 THEN month * 31 + day
       /* Same month, so we have to compare based on the day of the month */
       ELSE
         CASE 
           WHEN day - DAY(NOW()) < 0 THEN day + (12*31)
           ELSE day
       END CASE
     END CASE