Mysql Dayofyear in leap year

问题:

In the following query the leap year is not taken into account.

 SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(e.birthdate)
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(e.birthdate)

So in this query the birthdate of someone who is born in a leap year has got a different dayofyear in a non leap year.

How can i adjust the query to make sure it also works in a leap year?

The mysql version i have is: 5.0.67

回答1:

Where NOW() is a non-leap year 2011, the problem arises from anybody born on a leap year after February 29 will have an extra day because you are using DAYOFYEAR against the birth year.

DAYOFYEAR('2004-04-01') // DAYOFYEAR(e.birthdate) Returns 92
DAYOFYEAR('2011-04-01') // DAYOFYEAR(NOW()) Returns 91

Where you do DAYOFYEAR, you need the birthdate from the current year, not the year of birth.

So, instead of:

DAYOFYEAR(e.birthdate)

You can convert it to this year like this:

DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthdate)) YEAR))

Which converts a birthdate of:

'2004-04-01'

To:

'2011-04-01'

So, here's the modified query:

SELECT      e.id,
             e.title,
             e.birthdate
 FROM        employers e
 WHERE       DAYOFYEAR(curdate()) <= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))
 AND         DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(DATE_ADD(e.birthdate, INTERVAL (YEAR(NOW()) - YEAR(e.birthday)) YEAR))

People born on February 29th will fall on March 1st on non-leap years, which is still day 60.

回答2:

There are 365 days in a normal year, 366 in a leap year. In a normal year, March 1 would be the 60th day of the year. In a leap year, February 29 would be the 60th day of the year. The MySQL function is consistent.

If you really wanted to make it more complicated than it has to be, you could add a day to your DAYOFYEAR(curdate()) if curdate() is greater than or equal to March 1 AND curdate() isn't in a leap year. But I wouldn't recommend doing that.

回答3:

Here is a related solution

How to find the Birthday of FRIENDS Who are celebrating today using PHP and MYSQL celebrating-today-using-php-and-mysq

If I understand correctly, the problem you are having is that if your birthday is March 1, since you're looking for the (nth) 60th day of the year, you are getting the wrong day sometimes.

I believe the query in the above solution addresses the issue