[UPHPU] mysql date calculation bug

[Jim Anderson]

Ben Reece wrote:
> 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?

A good suggestion. I thought of that as well, but seem to remember 
coming across similar issues. I've had about 15 things in my head 
throughout the day so I may be getting some of my issues/projects 
schmeared though. Do you think doing it that way would skip over anyone? 
I saw that as an unlikely, but possible, concern.

i was thinking something like:

WHERE DATE_ADD(start_date, INTERVAL 6 MONTH) + INTERVAL 45 DAY = CURDATE()

but i haven't had the time to test it out.

to fill out the scenario a bit fuller, the criteria is this:

* 9 different emails. one for each of the following date scenarios.
* six months after sign-up minus 45 days
* six months after sign-up minus 30 days
* six months after sign-up minus 15 days
* six months after sign-up minus 5 days
* six months after sign-up [minus 0 days]
* six months after sign-up plus 15 days
* six months after sign-up plus 30 days
* six months after sign-up plus 45 days
* six months after sign-up plus 60 days

That is the schedule that i have in place.

-jim