Doctrine2 select a birthday

Question

I want to choose all the employees who have their birthday in the next 5 days. Birthday is stored in the date field. It seems to me that I need to use an intermediate, but then the range of the year destroys the result.

Basically, I want to select a date only by months and days for 5 days.

Database Schema:

CREATE TABLE IF NOT EXISTS `tbl_office_employee` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(256) collate utf8_unicode_ci default NULL, `surname` varchar(256) collate utf8_unicode_ci default NULL, `birthdate` date NOT NULL, `telephone` varchar(256) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`) ) 

Does anyone know of a single request to accomplish this?

Answer 1

You want something like this in MySQL ( edited - REALLY WORKING EXAMPLE):

 SELECT * FROM `tbl_office_employee` e WHERE FLOOR( ( UNIX_TIMESTAMP( CONCAT( YEAR(CURDATE()) + (DATE_FORMAT(e.birthdate, '%m-%d') < DATE_FORMAT(CURDATE(), '%m-%d')), DATE_FORMAT(e.birthdate, '-%m-%d'))) - UNIX_TIMESTAMP(CURDATE())) / 86400) < 5 

---

The SQL query below does not determine the birthday which is next year (i.e. January 1-5, when it is December 31st), so use the one above ...

SELECT * FROM tbl_office_employee e WHERE UNIX_TIMESTAMP (DATE_FORMAT (e.birthdate, CONCAT (YEAR (CURDATE ()), '-% m-% d'))) BETWEEN UNIX_TIMESTAMP (CURDATE ()) AND UNIX_TIMESTD (CATE) INTERVAL 5 DAY))

I had to use UNIX_TIMESTAMP due to daily changes from May 31 to June 5 (5 no more than 31) and change the year to e.birthdate .

---

This can be done in DQL (Doctrine 1):

 Doctrine_Query::create() ->select('e.firtsname') ->from('tbl_office_employee e') ->where('e.date BETWEEN ? AND ?', array($today_date, $date_plus_5_days)) ->getSqlQuery(); 

which should output basically the same thing.

I don’t think that DATE_ADD is available in DQL out of the box, but there is a chapter in the documents called DQL User Defined Functions, which has an example implementation of the DATE_ADD function for MySQL .

Answer 2

 SELECT * FROM member WHERE DATE_FORMAT(birthdate, '%m%d') between DATE_FORMAT(NOW(), '%m%d') and date_format(adddate(now(), interval 4 day),'%m%d'); 

or

 SELECT * FROM member WHERE DATE_FORMAT(`birthdate`, '%m%d') >= DATE_FORMAT(NOW(), '%m%d') AND DATE_FORMAT(`birthdate`, '%m%d') <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 4 DAY), '%m%d') ORDER BY DATE_FORMAT(`birthdate`, '%m%d') ASC; 

or

 SELECT * FROM member WHERE (1 = (FLOOR(DATEDIFF(DATE_ADD(DATE(NOW()),INTERVAL 4 DAY),birthdate) / 365.25)) -(FLOOR(DATEDIFF(DATE(NOW()),birthdate) / 365.25))) ORDER BY MONTH(birthdate),DAY(birthdate) 

All requests are tested.

Answer 3

Hm ... I have not tested it, but I can try ...

 SELECT *, CURRENT_DATE() + INTERVAL 5 DAY AS coming_date, CURRENT_DATE() AS today_date FROM tbl_office_employee WHERE birthdate <= coming_date AND birthdate >= today_date