[UPHPU] mysql date calculation bug

[Ben Reece]

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