Mysql Dayofyear in Leap Year
The next query does not take into account the leap year.
SELECT e.id,
e.title,
e.birthdate
FROM employers e
WHERE DAYOFYEAR(curdate()) <= DAYOFYEAR(e.birthdate)
AND DAYOFYEAR(curdate()) +14 >= DAYOFYEAR(e.birthdate)
Thus, in this matter, the birth of one who was born in a leap year received a different day during a non-leap year.
How can I customize the query to make sure it also works on leap years?
The mysql version I have is: 5.0.67
source to share
Where NOW()
is off-peak year 2011
, the problem occurs for anyone born in a leap year after Feb 29, there will be an extra day because you are using DAYOFYEAR
against the year of birth.
DAYOFYEAR('2004-04-01') // DAYOFYEAR(e.birthdate) Returns 92
DAYOFYEAR('2011-04-01') // DAYOFYEAR(NOW()) Returns 91
Where are you doing DAYOFYEAR
, you want the date of birth 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 the date of birth:
'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 during off-peak years, which is equal to today 60
.
source to share
There are 365 days in a normal year, 366 in a leap year. In a normal year, March 1 will be the 60th day of the year. In a leap year, February 29 will be the 60th day of the year. The MySQL function is agreed.
If you really wanted to make it more difficult than it should be, you can add a day to yours DAYOFYEAR(curdate())
if it is curdate()
greater than or equal to March 1 and curdate()
not in a leap year. But I would not recommend doing this.
source to share
Here is a related solution
How to find BIRTHDAY FRIENDS Who is celebrating today with 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 1st since you are looking for the (nth) 60th day of the year, sometimes you get the wrong day.
I suppose the request in the above solution fixes the problem
source to share