mySQL SELECT 即将到来的生日
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18747853/
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
mySQL SELECT upcoming birthdays
提问by Danny
I'm trying to write a query to select users of a database whose birthdays are in the next 7 days.
我正在尝试编写一个查询来选择生日在未来 7 天内的数据库用户。
I've done a lot of research but I can't come up with a working solution.
我做了很多研究，但我想不出一个可行的解决方案。
The birthday field is stored as a varchar eg '04/16/93' is there any way to work with this?
生日字段存储为 varchar 例如 '04/16/93' 有什么办法可以解决这个问题吗？
This is what I have so far:
这是我到目前为止：
SELECT *
FROM `PERSONS`
WHERE `BIRTHDAY` > DATEADD(DAY, -7, GETDATE())
I should have made it more clear, I'm trying to find birthdays not dates of birth. So i'm just looking for days and months not years.
我应该说得更清楚，我试图找到生日而不是出生日期。所以我只是在寻找几天和几个月而不是几年。
回答by Praveen Lobo
To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days.
要获得接下来 7 天内的所有生日，请将出生日期与今天之间的年差添加到出生日期，然后查找它是否在接下来的 7 天内。
SELECT *
FROM persons
WHERE DATE_ADD(birthday,
INTERVAL YEAR(CURDATE())-YEAR(birthday)
+ IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);
If you want to exclude today's birthdays just change >to >=
如果您想排除今天的生日，只需更改>为>=
SELECT *
FROM persons
WHERE DATE_ADD(birthday,
INTERVAL YEAR(CURDATE())-YEAR(birthday)
+ IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);
-- Same as above query with another way to exclude today's birthdays
SELECT *
FROM persons
WHERE DATE_ADD(birthday,
INTERVAL YEAR(CURDATE())-YEAR(birthday)
+ IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();
-- Same as above query with another way to exclude today's birthdays
SELECT *
FROM persons
WHERE DATE_ADD(birthday,
INTERVAL YEAR(CURDATE())-YEAR(birthday)
+ IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));
Here is a DEMOof all queries
这是所有查询的DEMO
回答by Rakesh
Its very easy and simple. No need to use any if conditions or anything else you just need to use DATE_FORMAT() function of mysql.
它非常容易和简单。无需使用任何 if 条件或其他任何内容，您只需要使用 mysql 的 DATE_FORMAT() 函数即可。
Here is my sql query that is
这是我的 sql 查询
SELECT id,email ,dob FROM `users` where DATE_FORMAT(dob, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') and DATE_FORMAT(dob, '%m-%d') <= DATE_FORMAT((NOW() + INTERVAL +7 DAY), '%m-%d')
回答by Todor
This is my solution. It also works if date of birth is January 1st and today's date is December 31.
这是我的解决方案。如果出生日期是 1 月 1 日，今天的日期是 12 月 31 日，它也适用。
SELECT `id`, `name`, `dateofbirth`,
DATE_ADD(
dateofbirth,
INTERVAL IF(DAYOFYEAR(dateofbirth) >= DAYOFYEAR(CURDATE()),
YEAR(CURDATE())-YEAR(dateofbirth),
YEAR(CURDATE())-YEAR(dateofbirth)+1
) YEAR
) AS `next_birthday`
FROM `user`
WHERE
`dateofbirth` IS NOT NULL
HAVING
`next_birthday` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY `next_birthday`
LIMIT 1000;
回答by Charles Bretana
try this:
尝试这个：
Select * From persons
where (DayOfYear(birthday) >= 7
And DayOfYear(birthday) - DayOfYear(curdate()) Between 0 and 6) Or
(MOD(YEAR(curDate()),4) = 0) And MOD(YEAR(curDate()),100) != 0
And (DayOfYear(birthday) + 366 - DayOfYear(curdate())) % 366 < 7) Or
(DayOfYear(birthday) + 365 - DayOfYear(curdate())) % 365 < 7)
回答by Danny
I managed to get it working with this query. Due mostly to the help of Lobo's answer.
我设法让它与这个查询一起工作。主要是由于 Lobo 的回答的帮助。
SELECT *
FROM persons
WHERE DATE_ADD(STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR(CURDATE())-YEAR(STR_TO_DATE(birthday, '%m/%d/%Y')) YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);
回答by Roey
I found this code to work really well:
我发现这段代码工作得很好：
DATE_ADD(user_birthdate, INTERVAL YEAR(FROM_DAYS(DATEDIFF(CURDATE(), user_birthdate)-1)) + 1 YEAR) AS next_birthday
it actually really simple, it calculate the person age, then the current's year birthday and then add 1 year.
其实很简单，就是先计算人的年龄，然后计算当前的生日，然后加上1年。
it based on the answer of Robert Eisele which you may find here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
它基于 Robert Eisele 的回答，您可以在这里找到：http: //dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
p.s.
ps
with this solution you might fetch people who had a birthday yesterday (that's because the -1 in the FROM_DAYS calculation, but it is needed because of the leap years). this shouldn't consider you too much since you only want 7 days a head so you should just add the following condition:
使用此解决方案，您可能会获取昨天过生日的人（这是因为 FROM_DAYS 计算中的 -1，但由于闰年而需要）。这不应该考虑你太多，因为你只想要 7 天，所以你应该添加以下条件：
HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
回答by Tom McQuarrie
The accepted answer above from Lobo is flawed in that, if the query is run on December 31, and the user has a birthday on January 1, they will not be matched.
上面从 Lobo 接受的答案存在缺陷，如果查询在 12 月 31 日运行，并且用户的生日是 1 月 1 日，则不会匹配它们。
You must add logic to the query so that, if the birthday has already passed this year, you look at NEXT YEAR's birthday, not this year's. Details below:
您必须向查询添加逻辑，以便如果今年的生日已经过去，您会查看 NEXT YEAR 的生日，而不是今年的生日。详情如下：
SELECT *, IF( DAYOFYEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) < DAYOFYEAR( NOW() ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) + 1 YEAR ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) YEAR ) )
AS nextBirthday FROM persons
WHERE nextBirthday BETWEEN CURDATE() AND DATE_ADD( CURDATE(), INTERVAL 7 DAY);
回答by Fanmade
We've had troubles with a calendar that had to show the next ten birthdays (including todays birthdays). I've reduced the solution I just found to the relevant parts:
我们在日历上遇到了麻烦，它必须显示接下来的十个生日（包括今天的生日）。我将刚刚找到的解决方案简化为相关部分：
SELECT first_name, last_name, birth_date,
IF (DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T')) < DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T')) ,
DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))+368 ,
DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))) AS upcomming
FROM users
WHERE birth_date IS NOT NULL AND birth_date !=0
ORDER BY upcomming ASC
LIMIT 0, 10
It sets every year (including the actual) to a leap year, so there won't be any problems with that. If you get close to the end of the year it's no problem, too. I'm quite statisfied to finally finding a working solution, so I wanted to share this, maybe it is of use for someone else :)
它将每年（包括实际）设置为闰年，因此不会有任何问题。如果你接近年底，那也没有问题。我很高兴终于找到了一个可行的解决方案，所以我想分享这个，也许对其他人有用:)
P.S.: If you don't want to show todays birthdays, just add a +1after DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T'))
PS：如果不想显示今天的生日，就加个+1afterDAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T'))
回答by RRK
While trying to get the list who have their b'days upcoming in a range of time, we can run in to couple of issues.
在尝试获取在某个时间范围内即将到来的生日名单时，我们可能会遇到几个问题。
When there is a leap year, then there is a chance that the condition you have fails to handle the case of leap years. Next problem could be like today is 2016-12-30and you need b'days for next 7 days. So the end of the period is in year 2017. Some condition fail in this case. These are very important test cases.
当有闰年时，您的条件可能无法处理闰年的情况。下一个问题可能就像今天一样2016-12-30，您需要在接下来的 7 天里休息几天。所以期间的结束是在 year 2017。在这种情况下，某些条件会失败。这些都是非常重要的测试用例。
Most of the fixed in this thread are using the DAYOFYEAR()which fails when you are on a leap year.
该线程中的大多数修复都使用了DAYOFYEAR()在闰年时失败的 。
eg.
例如。
DAYOFYEAR('2016-03-01 00:00:00')is 61.DAYOFYEAR('2015-03-01 00:00:00')is 60
DAYOFYEAR('2016-03-01 00:00:00')是61。DAYOFYEAR('2015-03-01 00:00:00')是60
Simplest and most easy to understand way is this.
最简单也最容易理解的方法就是这个。
- Calculate the next upcoming b'day for a user.
- Then check if the day comes in our range.
- 为用户计算下一个即将到来的 b'day。
- 然后检查这一天是否在我们的范围内。
This works on leap years and also the range of dates span in two years.
这适用于闰年以及两年内的日期范围。
SELECT *
FROM `PERSONS`
WHERE
/*
Here we calculate the next coming b'day for user
and check if it is between our span
*/
CONCAT(IF(
CONCAT( YEAR(CURDATE()), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`))) < CURDATE(),
YEAR(CURDATE()) + 1, /* Adds an year if already past */
YEAR(CURDATE()) /* Use this year if it is upcoming */
), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`)))
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL @tot DAY)
PS: Also it is the best solution if you want to order these on the basis of the b'days. You just need to add this as a field.
PS：如果您想在 b'days 的基础上订购这些，这也是最好的解决方案。您只需要将此添加为一个字段。
回答by VIVEK SEDANI
This is what I did when I faced the same problem :
当我遇到同样的问题时，这就是我所做的：
select * from users
where ( month(date_of_birth) > month(CURDATE())
and month(date_of_birth) < month(ADDDATE(CURDATE(),30)) )
or ( month(CURDATE()) = month(date_of_birth) and day(date_of_birth) >= day(CURDATE())
or month(ADDDATE(CURDATE(),30)) = month(date_of_birth) and day(date_of_birth) <= day(ADDDATE(CURDATE(),30)) )
or ( year(CURDATE()) > year(ADDDATE(CURDATE(),30)) and month(date_of_birth) < month(CURDATE()) and month(date_of_birth) > month(ADDDATE(CURDATE(),30)) )
