The choice of birthdays on MySQL + trivia

Given the task: choose from a database of users who have a birthday the other day, to display on the main page of the site.
We use PHP (ZF) & MySQL.

The field in our database is of type “date”, which is already good. The task is simple , I began to write ...
Faster, I think, google, why reinvent the wheel, has clearly been solved hundreds of times.
I looked at the first request - it works incorrectly, the second one too ...
Found the right one, but it turned out to be so monstrous.
There was no limit to surprise - some of these “bicycles” were invented this year!

I wrote in the end myself. For a start, simpler (birthdays from September 01 to October 29 are chosen here):

SELECT * FROM users__accounts WHERE DATE_FORMAT(birthday, '%m%d' ) >= '0901' AND DATE_FORMAT(birthday, '%m%d' ) <= '1029'

* This source code was highlighted with Source Code Highlighter .

')
But you need to set a period for the next N days. And what about the New Year's transition?
Wrote something like this:

SET @dayplus:=15;
SET @andor:= CASE WHEN YEAR (CURDATE() + INTERVAL @dayplus DAY ) - YEAR (CURDATE()) THEN 'OR' ELSE 'AND' END ;
SET @fromdate:=DATE_FORMAT(CURDATE(), '%m%d' );
SET @todate:=DATE_FORMAT(CURDATE() + INTERVAL @dayplus DAY , '%m%d' );
SET @birthday_query:=CONCAT(" SELECT * FROM users__accounts WHERE DATE_FORMAT(birthday, '%m%d' ) >= @fromdate ",
@andor, " DATE_FORMAT(birthday, '%m%d' ) <= @todate");
PREPARE birthday_query FROM @birthday_query;
EXECUTE birthday_query;
DEALLOCATE PREPARE archive_query;

* This source code was highlighted with Source Code Highlighter .

I decided that it is too early for me to write SQL procedures. In addition, the request formed on the Zend Framework will be easier. Happened:

class Users extends Zend_Db_Table_Abstract
{
protected $_name = 'users__accounts' ;
/**
...
*/
public function getBirthdayUsers($count, $days = 7)
{
$date = new Zend_Date();
/* */
$dateFrom = $date->toString( 'MMdd' );
/* ;) */
if ($dateFrom == '0301' && !$date->isLeapYear()) {
$dateFrom = '0229' ;
}
$yearFrom = $date->toString( 'YY' );
$date->addDay($days);
/* + $days */
$dateTo = $date->toString( 'MMdd' );

$nextYear = $yearFrom - $date->toString( 'YY' );

$select = $ this ->getAdapter()->select()
->from(array( 'u' => $ this ->_name))
->where( "date_format(u.birthday,'%m%d') >= ?" , $dateFrom)
->order( 'DAYOFYEAR(u.birthday)' )
->limit($count);
/* - OR / AND */
if ($nextYear == 0) {
$select->where( "date_format(u.birthday,'%m%d') < ?" , $dateTo);
} else {
$select->orWhere( "date_format(u.birthday,'%m%d') < ?" , $dateTo);
}

$users = $select->query()->fetchAll();
return $users;
}
}

* This source code was highlighted with Source Code Highlighter .

Constants are passed from PHP, and are not evaluated inside the request.
Now I will list the pitfalls of previously found bikes:
1. The transition to the new year was not taken into account.
2. The leap year was not taken into account when sampling by DAYOFYEAR () - an error on 1 day
3. Unfortunately, birthday, only once ... at 4 years
3. Too complicated calculations
3. Extreme dates fell out.

I think those who do not use ZF, also useful.
The SQL query itself turned out quite simple, but for very large databases, you need to start a separate field and index it. Time zones are not taken into account by the current task.

At the same time, I will give the age calculation in PHP (in MySQL, it is even easier). Because met implementations from calculation in seconds (with a leap error) to the “integral” two-page code.

/**
* @param timestamp() $birth
* @return INT
*/
function getAge($birth)
{

$now = time();
$age = date( 'Y' , $now) - date( 'Y' , $birth);
if (date( 'md' , $now) < date( 'md' , $birth)) {
$age--;
}

return $age;

}

* This source code was highlighted with Source Code Highlighter .

P.S. If there is a better bike, write. Then surely we will finish with the invention of bicycles of this type. :)

If you are minus, then put plus the best option from the comments, or offer your own.