All geek questions in one place

MySQL selects 7 days before the event and 7 days after the event

I have a database with birthday dates and usernames. I want to select all users who have a birthday / name from 7 days to 7 days in the future, this choice works, but I don’t know how to solve the problem with users who have a birthday / name on 31.12. eg. PHP will return "USER had a birthday 3 days before," this option works well just 7 days before the end of the year and 7 days after the new year. Thank you so much for your help.

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
+4
source share
3 answers

Good question. This is what I have done so far. I am not sure if it will be perfect, but this may be a good start. Give it a try.

 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)<right(curdate(),5),concat(year(curdate()),'-',right(birthday,5)),concat(year(curdate()-interval 1 year),'-',right(birthday,5))) as prev_birthday 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()
+1
source

I think this is pretty straight forward, use to_days (). This method depends on the calendar installed in the database, so you do not need to worry about leap years.

 select `name`, `surname`, `gender`, ('birthday') AS event, to_days(concat(year(current_date()),'-',month(`birthday`), '-',day_of_month(`birthday`))) as current_yr_bday FROM `users` WHERE to_days(concat(year(current_date()),'-',month(`birthday`), '-',day_of_month(`birthday`))) between to_days(date_sub(current_date() interval 7 days) and to_days(date_add(current_date() interval 7 days
+1
source

The brute force method should calculate your diff for YEAR-1, YEAR, YEAR + 1, and then check if any of these three sums satisfies your condition.

Not so brute force would be to make a difference between the present and the first Jan and calculate the years accordingly.

An interesting problem, I will come back if I come up with something else.

0
source

Source: https://habr.com/ru/post/1412433/

More articles:


All Articles