Search code examples
phpmysqlsqlselectdate-arithmetic

select users who their (timeStamped)birthday is X days later

I store birthday of my Users as Unix timestamp format(an integer number) in MySQL Database.

Now I want to send an email to users that their birthday is 30 days later.

I know that below SQL Code is to select users who their birthday is today : 

SELECT * FROM members WHERE 
DATE_FORMAT(FROM_UNIXTIME(birthday),'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')

but I don't know how to change it for my purpose.

I googled too much and found this similar question on SO But all answers are based on DATE formatted birthday while my birthday field is based on timeStamp created by time().

have you any solution for that?

Solution

  • You could use the datediff function:

    SELECT * 
FROM   members 
WHERE  DATEDIFF(FROM_UNIXTIME(birthday), NOW()) <= 30