MySQL查询即将到来的生日。

Question

我正在尝试编写一个查询来选择数据库中生日在接下来的7天内的用户。

我已经做了很多研究，但是我无法想出一个可行的解决方案。

生日字段存储为varchar，例如'04/16/93'，有没有办法处理这个？

到目前为止，这就是我所拥有的：

SELECT * 
FROM   `PERSONS` 
WHERE  `BIRTHDAY` > DATEADD(DAY, -7, GETDATE()) 

我应该更加明确，我想找的是生日而不是出生日期。因此，我只是寻找日期和月份，而不是年份。

- Danny

Answer 1

要获得接下来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);

如果你想排除今天的生日，只需将>更改为>=。

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

这里是所有查询的演示

- Praveen Lobo

---

第一个查询只会返回新生儿，第二个查询将只返回未来出生的人.... - Hart CO

@GoatCO 哎呀！看了一下问题和提供的查询，我只是假设当前年份的生日已经存储在表中。无论如何，我更新了答案。希望可以解决问题。 - Praveen Lobo

@DannyWalsh 正如你已经发现的那样，你需要使用 STR_TO_DATE(birthday, '%m/%d/%Y') 将字符串转换为日期格式，并在上述查询中使用它来代替 birthday。 - Praveen Lobo

如果您在12月31日运行此脚本，而生日是1月1日，则此脚本将无法正常工作。 - Tom McQuarrie

恐怕这个解决方案也存在闰年问题（请参考我对托多尔回答的评论中的例子）。常见的问题是三月至十二月生日的DAYOFYEAR在闰年和非闰年之间有所不同。 - Hagen von Eitzen

Answer 2

非常容易和简单。不需要使用任何if条件或其他内容，只需使用mysql的DATE_FORMAT()函数。

这是我的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')

---

- Rakesh

---

当你试图获取下一年的未来生日时，实际上这并不起作用。 - jck

Answer 3

这是我的解决方案。如果出生日期是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;

- Todor

---

如果出生年份是闰年而当前年份不是，或者相反情况下，就会导致计算错误。例如，如果出生日期为2000年7月4日，当前日期为2015年7月5日，则计算出的下一个生日日期会是2015年7月4日，而不是2016年7月4日（对于查询下一周的生日并没有影响）；但是如果出生日期为2001年7月4日，当前日期为2016年7月4日，则计算出的下一个生日日期就是2015年7月4日，而不是2016年7月4日。 - Hagen von Eitzen

好的，@HagenvonEitzen，我会尽力修复这个问题。 - Todor

Answer 4

我做了很多“研究”，这是我的解决方案，我觉得它非常容易理解！

SELECT *,
(366 + DAYOFYEAR(birth_date) - DAYOFYEAR(NOW())) % 366 as left_days
FROM `profile`
ORDER BY left_days;

- Rasulbek Abdurasulov

---

有时这会返回昨天的生日，left_days 为 0 :-( - Peter

Answer 5

使用DAYOFYEAR()函数计算出生日期的解决方案在闰年出生时会存在缺陷。在处理年龄或生日时，请考虑使用TIMESTAMPDIFF()函数。

当前年龄可以通过以下方式计算：

timestampdiff(YEAR, person.date_of_birth, curdate())

通过将年龄加上出生日期来计算即将到来的生日。

DATE_ADD(
    person.date_of_birth, 
    INTERVAL timestampdiff(YEAR, person.date_of_birth, curdate())+1 YEAR
)

将这些内容结合起来，以解决OP的疑问。

select
    DATE_ADD(
        person.date_of_birth, 
        INTERVAL timestampdiff(YEAR, date_add(person.date_of_birth,INTERVAL 1 DAY), curdate())+1 YEAR
    ) upcoming_birthday
from person
having upcoming_birthday between curdate() and DATE_ADD(curdate(), INTERVAL 7 DAY)

请注意，我在计算年龄时将date_of_birth加了一天，否则今天生日的人将不会被返回。

- Ian

---

这是我见过的唯一正确的答案。其他答案在跨年、闰年等方面都无法正常工作。 - Anthony

你很厉害，这里使用的大多数函数都不在w3schools上。 - Richard Muvirimi

你真是个天才，这里使用的大部分功能在w3schools上都找不到。 - undefined

Answer 6

我发现这段代码运行得非常好：

DATE_ADD(user_birthdate, INTERVAL YEAR(FROM_DAYS(DATEDIFF(CURDATE(), user_birthdate)-1)) + 1 YEAR) AS next_birthday 

这是一个简单的计算年龄的方法，它会先计算出人的年龄，然后计算出当前年份的生日，最后加上1岁。这个方法基于Robert Eisele的答案，你可以在这里找到：http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html。

注意，使用这个方法可能会抓取到昨天过生日的人（因为FROM_DAYS计算中的-1），但这是必需的，因为要考虑闰年。不过这应该不会对你造成太大影响，因为你只需要往后算7天，所以只需要添加以下条件即可：

HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) 

- Roey

Answer 7

在尝试获取一定时间范围内即将过生日的人员列表时，我们可能会遇到几个问题。

当有闰年时，您的条件可能无法处理闰年的情况。下一个问题可能是今天是2016-12-30，您需要未来7天内的生日。因此，该期间的结束在2017年。这种情况下某些条件会失败。这些是非常重要的测试用例。

本主题中的大多数修复程序都使用DAYOFYEAR（），但在闰年时会失败。

例如：

DAYOFYEAR('2016-03-01 00:00:00') 是61。
DAYOFYEAR('2015-03-01 00:00:00') 是60

最简单、易于理解的方法是：

1. 计算用户下一个即将过生日的日期。
2. 然后检查该日期是否在我们的时间范围内。

这适用于闰年和跨越两年的日期范围。

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)

提示：如果您想按照生日日期来排序，这也是最好的解决方案。您只需要将其添加为一个字段即可。

- rrk

---

DAYOFYEAR 对我很有用。你让我的一天变得美好并拯救了我的生命，谢谢亲爱的 :) - Kamlesh

Answer 8

试一下这个：

 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)

- Charles Bretana

---

修复闰年问题，现在已解决。 - Charles Bretana

Answer 9

我用这个查询成功地解决了问题，主要得益于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);

- Danny

---

有一个缺陷，现在已经修复。 - Praveen Lobo

Answer 10

这是我在面临相同问题时所做的事情：

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

- VIVEK SEDANI