Use MySQL to determine whether today is a user's birthday_问答_开发者

I have all users' birt开发者_开发问答hdays 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.

This should work:

   SELECT * 
      FROM USERS
      WHERE 
         DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')

Here is an answer that property takes into account leap-years and will always give you the users whose birthday is on the 29th of February at the same time as those on the 1st of March.

SELECT * 
  FROM USERS
  WHERE 
     DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
     OR (
            (
                DATE_FORMAT(NOW(),'%Y') % 4 <> 0
                OR (
                        DATE_FORMAT(NOW(),'%Y') % 100 = 0
                        AND DATE_FORMAT(NOW(),'%Y') % 400 <> 0
                    )
            )
            AND DATE_FORMAT(NOW(),'%m-%d') = '03-01'
            AND DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = '02-29'
        )

Since this gets more and more to be a code-golf question, here's my approach on solving this including taking care of the leap years:

select * 
from user
where (date_format(from_unixtime(birthday),"%m-%d") = date_format(now(),"%m-%d"))
   or (date_format(from_unixtime(birthday),"%m-%d") = '02-29'
       and date_format('%m') = '02' 
       and last_day(now()) = date(now())
      );

Explanation: The first where clause checks if somebody's birthday is today. The second makes sure to only select those whose birthday is on Feb 29th only if the current day equals the last day of February.

Examples:

SELECT last_day('2009-02-01'); -- gives '2009-02-28'
SELECT last_day('2000-02-01'); -- gives '2009-02-29'
SELECT last_day('2100-02-01'); -- gives '2100-02-28'

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 */

You can use the query below if date of birth stored in a table.

Today Birthday :

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(CURDATE())
   and MONTH(FIELDNAME) = MONTH(CURDATE());

Yesterday Birthday:

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY))
   and MONTH(FIELDNAME) = MONTH(CURDATE());

Tomorrow Birthday:

select * from TABLENAME
 where DAY(FIELDNAME) = DAY(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
   and MONTH(FIELDNAME) = MONTH(CURDATE());

I come across with this problem, and I just used this simple code using the NOW();

$myquery = "SELECT username FROM $tblusers WHERE NOW() = bd";

The results are today's birthdays so after that I working in sending emails to my users on their birthday.

I store my users bithdays using just the DATE so I always have yy:mm:dd, so this works like a charm, at least to me, using this approach.

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'

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.

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.

Enjoy :)

select p.birthday, 
CASE YEAR(p.birthday)%4 + MONTH(p.birthday)-2 + dayofmonth(p.birthday)-29 WHEN 0 THEN 1 ELSE 0 END as isBirthday29Feb,
CASE YEAR(now())%4  WHEN 0 THEN 1 ELSE 0 END as isThisYearLeap,
IF(YEAR(p.birthday)%4 + MONTH(p.birthday)-2 + dayofmonth(p.birthday)-29=0 AND YEAR(now())%4 != 0,
            DATE_ADD(DATE_ADD(p.birthday, INTERVAL 1  DAY), INTERVAL YEAR(NOW())-YEAR(p.birthday)  YEAR) ,
            DATE_ADD(p.birthday, INTERVAL YEAR(NOW())-YEAR(p.birthday)  YEAR)  
)as thisYearBirthDay
from person p;

This gives you a person's birthday calculated according the current year. Then you can use it for other calculations! The columns isBirthday28Feb and isThisYearLeap are given just to illustrate the solution.

I took Saggi's answer and thought about modifying it so that it would show birthdays for next 7 days and noticed it also neatly solves the leap year problem :)

SELECT * 
   FROM USERS
   WHERE 
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') >= DATE_FORMAT(NOW(),'%m-%d') AND
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') <  DATE_FORMAT(NOW()+INTERVAL 1 DAY,'%m-%d')

Note it doesn't include the upper bound. For leap years nothing falls between '02-28' and '02-29', but for non-leap years the '02-29' falls between '02-28' and '03-01'.

If you'd want the next 7 days, use:

SELECT * 
   FROM USERS
   WHERE 
      DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') BETWEEN
      DATE_FORMAT(NOW(),'%m-%d') AND DATE_FORMAT(NOW()+INTERVAL 7 DAY,'%m-%d')

SELECT * FROM user WHERE DATE_FORMAT((birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')

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

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);

Simple way

SELECT * FROM users WHERE MONTH(brith_day_table) = MONTH(NOW()) AND DAY(birth_day_table) = DAY(NOW())

What you are doing currently is determine if today is users birthday using sql and if yes send the wish separately, there is better approach to handling this.

Extract the wish details in excel
let Wishing Application take care of the rest

At minimal it just need two things excel file with wish details (Date, name, email) and a configuration file (application.properties) and that is it, you are good to go.

Further there various options to run the application locally (Command line, foreground, background, docker, windows scheduler, unix cron etc) Cloud.

Application is highly configurable , you can configure various details like:

Workbook loading options
Image options to send with wishes.
SMTP Configurations
Other application level configurations like, when to send wish, belated wish, logging etc.

Disclaimer : I am the owner of the application