Doctrine2 select birthday range

Question

I want to select all employees who have their birthday the upcoming 5 days. The birthday is saved in a date field. It feels like I have to use a between, but then the year range ruins the result.

Basically I want to select a date by month and day only, in a range of 5 days.

Database scheme:

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 anybody know a single query 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

---

SQL query written below doesn't select birthday which are next year (i.e. on 1-5th Jan, when it's 31st Dec), so use 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_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 5 DAY))

I had to use UNIX_TIMESTAMP, because of day changes i.e from 31st May to 5th June (5 isnt't greater than 31) and change year in e.birthdate.

---

It 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.

I don't think DATE_ADD is availible in DQL out of the box, but there is chapter called DQL User Defined Functions in docs, which has example implementation of 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 query's are tested.