CODEWITHSUNDEEP
mysqlevents
Michal Tinka
Michal Tinka

Reputation: 165

MySQL select 7 days before event and 7 days after event

I have database with dates of birthdays and namesdays of users. I want to select all users who had birthday/namesday from 7 days ago to 7 days in future this select works but I don't know how to solve the problem with users who have birthday/namesday 31.12. for example. PHP will return "USER had birthday 3 days before", this select works well only 7 days before end of year and 7 days after new year. Thank you very much for 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

Upvotes: 4

Views: 735

Answers (3)

kirbs
kirbs

Reputation: 168

I think this is fairly straight forward, use to_days(). This way relies on the calendar set in the DB, so you don't have 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

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56377

Nice question. This is what I've done so far. I'm not sure if it's perfect but it could 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()

Upvotes: 1

Philippe
Philippe

Reputation: 446

Brute force method would be to calculate your diff for YEAR-1, YEAR, YEAR+1 and then check if any of these 3 sums satisfies your condition.

Not so brute force would be to do the difference between now and first of Jan and calculate years accordingly to that.

Interesting problem, i'll come back if i think of something else.

Upvotes: 0

Related Questions