Use MySQL to determine whether today is a user'

问题:

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.

回答1:

This should work:

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

回答2:

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

回答3:

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'

回答4:

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.

回答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'

回答6:

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.

回答7:

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.

回答8:

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

回答9:

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.

回答10:

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

回答11:

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

回答12:

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

回答13:

Check this URL: http://www.tizag.com/mysqlTutorial/mysql-date.php