使用 MySQL 判断今天是否是用户的生日
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2218378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use MySQL to determine whether today is a user's birthday
提问by James Simpson
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.
我将所有用户的生日都存储为 UNIXtimestamp，我想每天向当天生日的用户发送电子邮件。
I need to make a MySQL query that will get all of the rows that contain a birthday on today's date.
我需要进行一个 MySQL 查询，以获取包含今天日期的生日的所有行。
It seems like this should be fairly simple, but maybe I am just overcomplicating it.
看起来这应该相当简单，但也许我只是把它复杂化了。
回答by Saggi Malachi
This should work:
这应该有效：
SELECT *
FROM USERS
WHERE
DATE_FORMAT(FROM_UNIXTIME(birthDate),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
回答by Andrew Moore
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.
这里有一个回答，property 考虑了闰年，并且总是给你生日在 2 月 29 日的用户和 3 月 1 日的用户。
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'
)
回答by Dan Soap
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.
解释：第一个 where 子句检查某人的生日是否是今天。第二个确保仅在当前日期等于 2 月的最后一天时才选择生日在 2 月 29 日的那些人。
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'
回答by Arth
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 */
回答by djburner
I come across with this problem, and I just used this simple code using the NOW();
我遇到了这个问题，我只是使用了这个简单的代码 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.
我只使用 DATE 存储我的用户生日，所以我总是有yy:mm:dd，所以使用这种方法，这就像一个魅力，至少对我来说。
回答by EngineHeart
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 isBirthday28Feband isThisYearLeapare given just to illustrate the solution.
这会为您提供根据当前年份计算的某人的生日。然后您可以将其用于其他计算！列isBirthday28Feb和isThisYearLeap仅用于说明解决方案。
回答by Bhavik Koradiya
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());
回答by Thomas K Pedersen
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.
位 '(dayofyear(date_format(current_date,'%Y-03-01'))-60)' 自 3 月 1 日起在闰年返回 1 将是 dayofyear 数字 61，在正常年份返回 0。
From here it's just a matter of substracting that extra day to the "is-it-my-birthday"-calculation.
从这里开始，只需将额外的一天减去“这是我的生日”计算。
回答by Jakob Borg
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.
下面的答案实际上不起作用。它没有考虑到一年是 365.24（偶尔有闰日）天这一事实，因此与用户生日的实际比较至少可以说是复杂的。由于历史原因，我要离开它。
The other answers should work but if you want a slight optimization, say if there are many manyrows, 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 + 86400and then select records where the timestamp is between those values.
其他答案应该有效，但是如果您想要稍微优化，例如如果有很多行，那么您最好直接在时间戳秒内表达查询。您可以使用关系（由于考虑到时区而略有涉及）：
today_starts = UNIX_TIMESTAMP(NOW()) - TIMESTAMPDIFF(SECOND, DATE(NOW()), NOW()) today_ends = today_starts + 86400然后选择时间戳在这些值之间的记录。
回答by Pentium10
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.
我接受了 Saggi Malachi 的回答，并将 2 月 29 日的生日扩展到 2 月 28 日的日期，如果在那一年没有这样的日子。
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'
