[UPHPU] mysql date calculation bug

[Jennifer Charrey]

Ben Reece wrote:
> Jim Anderson wrote:
>> while people are discussing mysql, i have a problem that i would like 
>> to pose to the group in hopes that someone has come across the solution.
>>
>> when running the following, a different date should be returned for each:
>>
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 44 DAY), INTERVAL 6 MONTH);
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 45 DAY), INTERVAL 6 MONTH);
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 46 DAY), INTERVAL 6 MONTH);
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 47 DAY), INTERVAL 6 MONTH);
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 48 DAY), INTERVAL 6 MONTH);
>> SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 49 DAY), INTERVAL 6 MONTH);
>>
>> however, for the middle 4 queries, the same date [2007-02-28] is 
>> returned. my natural assumption here is that i have uncovered a bug in 
>> the way that mysql handles the short [and leap year-able] month of 
>> february.
>>
>> has anyone else encountered this and if so, any luck with a solution?
>>
>> thnx,
>> -ja
>>
>> ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
>> Jim Anderson
> Jim,
> 
> I think this is the proper functionality.  The middle 4 queries are 
> trying to subtract 6 months from:
> 
> 2007-08-31
> 2007-08-30
> 2007-08-29
> 2007-08-28
> 
> Since 6 months prior to Aug is Feb, and the last day of Feb is the 28th, 
> it makes sense to me that Feb 28 would be six months prior to Aug 31.  
> The alternative seems to be some time in early March, but that would 
> only be 5 months previous, which makes no sense.
> You get the same result if you try to go to a month with only 30 days -- 
> DATE_SUB( DATE_SUB(CURDATE(), INTERVAL 45 DAY), INTERVAL 2 MONTH) will 
> return 2007-06-30, so I don't think it has anything to do with a 
> February/leap year problem.
> 
> Ben

This is expected behavior and you can find it in the MySQL documentation:

 >  If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a 
day that is larger than the maximum day for the new month, the day is 
adjusted to the maximum days in the new month:
 >
 > mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
 >         -> '1998-02-28'

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

As Ben said, a different answer probably wouldn't even make sense. March 
3 would not be 6 months ago.

Jennifer