Use MySQL to determine whether today is a user'

Question

I have all users' birthdays stored as a UNIXtimestamp and am wanting to send out e-mails each day to users that have a birthday that day.

I need to make a MySQL query that will get all of the rows that contain a birthday on today's date.

It seems like this should be fairly simple, but maybe I am just overcomplicating it.

Answer 1

The answer below doesn't actually work. It doesn't take into account the fact that a year is 365.24 (leap days now and then) days long, so the actual comparison against the users birthdate is complicated to say the least. I'm leaving it for historical reasons.

The other answers should work but if you want a slight optimization, say if there are many many rows, you are probably better off expressing the query directly in timestamp seconds. You can use the relations (slightly involved because of taking timezone into account):

today_starts = UNIX_TIMESTAMP(NOW()) - TIMESTAMPDIFF(SECOND, DATE(NOW()), NOW())
today_ends = today_starts + 86400

and then select records where the timestamp is between those values.

Answer 2

set @now=now();
select * from user where (month(birthday) = month(@now) and day(birthday) = day(@now)) or
  (month(birthday) = 2 and day(birthday) = 29 and month(@now) = 2 and day(@now) = 28 and
  month(date_add(@now, interval 1 day)) = 3);

Answer 3

Here's my contribution

SELECT
  DAYOFYEAR(CURRENT_DATE)-(dayofyear(date_format(CURRENT_DATE,'%Y-03-01'))-60)=
  DAYOFYEAR(the_birthday)-(dayofyear(date_format(the_birthday,'%Y-03-01'))-60)
FROM
   the_table

The bits '(dayofyear(date_format(current_date,'%Y-03-01'))-60)' returns 1 on leap years since march 1st will be dayofyear number 61, and 0 on normal years.

From here it's just a matter of substracting that extra day to the "is-it-my-birthday"-calculation.

Answer 4

Couldn't you just select all rows that matched the current day's date? You could also use the FROM_UNIXTIME() function to convert from unix timestamp to Date:

mysql> SELECT FROM_UNIXTIME(1196440219); -> '2007-11-30 10:30:19'

This is documented from http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

Answer 5

I took Saggi Malachi's answer and extended to include a birthday on 29th February into 28th February date, if in that year there is no such day.

SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
UNION
SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(NOW(),'%Y')%4 != 0 AND DATE_FORMAT(NOW(),'%m-%d')='02-28' and DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = '02-29'

Answer 6

This should cover the leap year cases, and uses the internal date mechanics.

Basically it works by adding the years between the two dates to the date of birth and checks for equality with the current date:

WHERE dob + INTERVAL (YEAR(CURDATE()) - YEAR(dob)) YEAR = CURDATE();

Testing:

SELECT '2012-02-29' 
       + INTERVAL (YEAR('2015-02-28') - YEAR('2012-02-29')) YEAR 
       = '2015-02-28'; /* 1, is birthday */

SELECT '2012-02-28' 
       + INTERVAL (YEAR('2015-02-28') - YEAR('2012-02-28')) YEAR  
       = '2015-02-28'; /* 1, is birthday */

SELECT '2012-02-28'
       + INTERVAL (YEAR('2016-02-29') - YEAR('2012-02-28')) YEAR 
       = '2016-02-29'; /* 0, is NOT birthday  */

SELECT '2012-02-29'
       + INTERVAL (YEAR('2016-02-29') - YEAR('2012-02-29')) YEAR 
       = '2016-02-29'; /* 1, is birthday */