mysql birthday reminder, leap year

Question

Hi, I'm trying to sort out a result set that gives the 5 closest users sorted by upcoming birthday. This works perfectly until leap years comes into play. For example:

• May 15th - 96 days left
• May 15th - 97 days left

The top result is a birth at 1987 and the lower is from 1988. u_birth is stored as yyyy-mm-dd. Is there a simple way to sort this problem without having to rewrite the entire query?

SELECT u_birth, IF( DAYOFYEAR( u_birth ) >= DAYOFYEAR( NOW() ), 
          DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ), 
          DAYOFYEAR( u_birth ) - DAYOFYEAR( NOW() ) +  
      DAYOFYEAR( CONCAT( YEAR( NOW() ), '-12-31' ) ) 
 )  
 AS distance
FROM (blog_users)
WHERE `s_agehide` = 0
ORDER BY distance ASC
LIMIT 5

This query is taken and modified from the mysql manual: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#c7489

Answer 1

Don't use dayofyear as anything after Feb 29th in a leap year is one day further on than usual. Instead, extract the month and day and concat them together with today's year. Then do the comparison.

Like so:

SELECT u_birth, 
  IF(DATE(CONCAT(YEAR(NOW()),'-',MONTH(u_birth),'-',DAY(u-birth))) >= DATE(NOW()),
...

ETA:

Just so I don't have to rewrite the expression for converting birth date to birthday, I'll stick it in a variable. I recommend you write a function that does the conversion so you can use it in a query directly.

SET @birthday= SELECT DATE(CONCAT(YEAR(NOW()),'-',MONTH(u_birth),'-',DAY(u-birth))) FROM users WHERE user_id=x; //obviously only good for one value, but use the example for the calculation

SELECT u_birth, 
       IF(DATE(@birthday)>=DATE(NOW()), 
          DATEDIFF(DATE_ADD(@birthday, INTERVAL 1 YEAR),NOW()), 
          DATEDIFF(NOW(),@birthday)
       ) as days
FROM users WHERE user_id=x;

Answer 2

There's obviously a problem if your algorithm depends on the year of birth of the person. To workaround this, first find each person's next birthday after the current date, then calculate the difference between that date and now.

SELECT u_birth, DATEDIFF(next_birthday, NOW()) AS distance FROM (
    SELECT *, ADDDATE(birthday, INTERVAL birthday < DATE(NOW()) YEAR) AS next_birthday
    FROM (
        SELECT *, ADDDATE(u_birth, INTERVAL YEAR(NOW()) - YEAR(u_birth) YEAR) AS birthday
        FROM blog_users
        WHERE s_agehide = 0
    ) AS T1
) AS T2
ORDER BY distance ASC
LIMIT 5

Results:

'1992-02-29', 20
'1993-03-01', 21
'1987-05-15', 96
'1988-05-15', 96
'1988-09-18', 222

Test data:

CREATE TABLE blog_users (u_birth NVARCHAR(100) NOT NULL, s_agehide INT NOT NULL);
INSERT INTO blog_users (u_birth, s_agehide) VALUES
('1987-05-15', 0),
('1988-05-15', 0),
('1988-09-20', 0),
('2000-01-02', 0),
('2000-01-03', 1),
('1988-09-19', 0),
('1988-09-18', 0),
('1992-02-29', 0),
('1993-03-01', 0);

Note that someone born on a leap day is assumed to have a birthday of 28th February on a non-leap year.

Also, your query doesn't include the user id of the user. You probably want to add this too, I'd imagine.