mysql前7天内,MySQL在事件发生前7天和事件发生后7天选择

我有数据库,其中包含生日和用户名称日期.我想选择从7天前到7天生日/名字日的所有用户此选择工作,但我不知道如何解决有生日/名字日31.12的用户的问题.例如.

PHP将返回“用户生日前3天”,此选项仅在年底前7天和新年后7天内正常运行.非常感谢你的帮助.

SELECT `name`, `surname`, `gender`, ('birthday') AS event,

DATEDIFF(NOW(), DATE(REPLACE(`birthday`, YEAR(`birthday`), YEAR(NOW())))) AS diff

FROM `users`

WHERE DATEDIFF(NOW(), DATE(REPLACE(`birthday`, YEAR(`birthday`), YEAR(NOW()))))

BETWEEN -7 AND 7

UNION

SELECT `name`, `surname`, `gender`, ('namesday') AS event,

DATEDIFF(NOW(), DATE(REPLACE(`namesday`, YEAR(`namesday`), YEAR(NOW())))) AS diff

FROM `users`

WHERE DATEDIFF(NOW(), DATE(REPLACE(`namesday`, YEAR(`namesday`), YEAR(NOW()))))

BETWEEN -7 AND 7

最佳答案 好问题.

这就是我到目前为止所做的.我不确定它是否完美但它可能是一个好的开始.试试看.

select *,

if(right(birthday,5)>=right(curdate(),5),concat(year(curdate()),'-',right(birthday,5)),concat(year(curdate()+interval 1 year),'-',right(birthday,5))) as next_birthday,

if(right(birthday,5)

from users

having next_birthday

between curdate() - interval 7 day and curdate() + interval 7 day

or prev_birthday

between curdate() - interval 7 day and curdate()