我正在尝试编写一个查询来选择数据库中生日在接下来的7天内的用户。
我已经做了很多研究,但是我无法想出一个可行的解决方案。
生日字段存储为varchar,例如'04/16/93',有没有办法处理这个?
到目前为止,这就是我所拥有的:
SELECT *
FROM `PERSONS`
WHERE `BIRTHDAY` > DATEADD(DAY, -7, GETDATE())
我应该更加明确,我想找的是生日而不是出生日期。因此,我只是寻找日期和月份,而不是年份。
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()));
这里是所有查询的演示
非常容易和简单。不需要使用任何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')
这是我的解决方案。如果出生日期是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;
SELECT *,
(366 + DAYOFYEAR(birth_date) - DAYOFYEAR(NOW())) % 366 as left_days
FROM `profile`
ORDER BY left_days;
使用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
)
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_ADD(user_birthdate, INTERVAL YEAR(FROM_DAYS(DATEDIFF(CURDATE(), user_birthdate)-1)) + 1 YEAR) AS next_birthday
HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
在尝试获取一定时间范围内即将过生日的人员列表时,我们可能会遇到几个问题。
当有闰年时,您的条件可能无法处理闰年的情况。下一个问题可能是今天是2016-12-30
,您需要未来7天内的生日。因此,该期间的结束在2017
年。这种情况下某些条件会失败。这些是非常重要的测试用例。
本主题中的大多数修复程序都使用DAYOFYEAR()
,但在闰年时会失败。
例如:
DAYOFYEAR('2016-03-01 00:00:00')
是61
。
DAYOFYEAR('2015-03-01 00:00:00')
是60
最简单、易于理解的方法是:
这适用于闰年和跨越两年的日期范围。
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)
提示:如果您想按照生日日期来排序,这也是最好的解决方案。您只需要将其添加为一个字段即可。
DAYOFYEAR
对我很有用。你让我的一天变得美好并拯救了我的生命,谢谢亲爱的 :) - Kamlesh试一下这个:
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)
我用这个查询成功地解决了问题,主要得益于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);
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)) )
STR_TO_DATE(birthday, '%m/%d/%Y')
将字符串转换为日期格式,并在上述查询中使用它来代替birthday
。 - Praveen LoboDAYOFYEAR
在闰年和非闰年之间有所不同。 - Hagen von Eitzen