[UPHPU] mysql date calculation bug

[Ben Reece]

Jim Anderson wrote:
> I have a client base that certain emails must be sent out a various 
> days befor and after 6 months from the date that they sign up for 
> service. With that in mind, I created a system that handles the 
> requirements however, I found this issue this morning. This issue 
> results in an email getting sent [worst case scenario of] 4x in a row 
> [1 a day for 4 days.] Best case [error] scenario is only 2x, but still 
> bad. Don't want to flood one's inbox with frivolity.
>
> My fix, considering this snag, is to check the client logs and check 
> to see is the email was sent within the last 4 days and, if so, don't 
> send again.
>
> I hate this fix as it is clunky and not at all pretty, but I can't 
> think of another way to do it. If anyone has any suggestions, please 
> feel free to pipe in.
>
> Thanks for asking Ben,
> -jim
What if you did it in reverse?  That is, instead of checking if they 
signed up 6 months ago, check if today is 6 months after their signup?  
So instead of the DATE_SUB, you do something like:

WHERE DATE_ADD('2007-02-28', INTERVAL 6 MONTH) = signup_date

That way you'd send out the email on Aug. 28th, but not on the 29th, 
30th, or 31st.  Would that work?

Ben