Extract birthdays in kind

Question

Given the following 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 am trying to extract users in order of their birthdays, primarily in the coming birthdays. So, if the request is executed on September 16th, the order should be: jane, bob, joe, john, mike.

Answer 1

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 expression ORDER BYtakes the logical result of comparing the birthday of the current year with the current date and adds it to the current year.

This leads to the next birthday (expressed as DATE), on which the operator can be ordered.

As a bonus, you can easily get your 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 request handles better times.

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;

, , Feb 29, Feb 28.

Alex, 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 2

, :

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

, , , - :

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

ORDER BY , ​​, , .

Answer 3

, , , - - , , . MySQL STR_TO_DATE. IF, . - :

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;

, , . , , DATE.

Answer 4

- . - .

, , 31 (). , * 31 . . .

: . , 12 "" (31-) .

.

 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

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

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()))
   );

, , , 365 , , .

Answer 7

therefore, the index will include only one 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