Bug #8108 Problem with date and interval calculations
Submitted: 24 Jan 2005 12:42 Modified: 27 May 2005 23:30
Reporter: Frank Mussmann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.9 OS:Any (*)
Assigned to: Jim Winstead CPU Architecture:Any

[24 Jan 2005 12:42] Frank Mussmann
Description:
I have tested a little bit with the INTERVAL Function, and I found something that is strange. (It's about leap years.)
When calculating with INTERVAL the February results strange behaviour.

How to repeat:
Here are the SQL-Statements:
select "------";
select now();
select "------";
select date(now() + interval 2 day + interval + 1 month);
select date(now() + interval 3 day + interval + 1 month);
select date(now() + interval 4 day + interval + 1 month);
select date(now() + interval 5 day + interval + 1 month);
select date(now() + interval 6 day + interval + 1 month);
select date(now() + interval 7 day + interval + 1 month);
select date(now() + interval 8 day + interval + 1 month);
select date(now() + interval 9 day + interval + 1 month);
select "------";
select date(now() + interval 2 day + interval + 1 month - interval 1 year);
select date(now() + interval 3 day + interval + 1 month - interval 1 year);
select date(now() + interval 4 day + interval + 1 month - interval 1 year);
select date(now() + interval 5 day + interval + 1 month - interval 1 year);
select date(now() + interval 6 day + interval + 1 month - interval 1 year);
select date(now() + interval 7 day + interval + 1 month - interval 1 year);
select date(now() + interval 8 day + interval + 1 month - interval 1 year);
select date(now() + interval 9 day + interval + 1 month - interval 1 year);

================
Here are the results:
------
------
now()
2005-01-24 13:41:12
------
------
date(now() + interval 2 day + interval + 1 month)
2005-02-26
date(now() + interval 3 day + interval + 1 month)
2005-02-27
date(now() + interval 4 day + interval + 1 month)
2005-02-28
date(now() + interval 5 day + interval + 1 month)
2005-02-28
date(now() + interval 6 day + interval + 1 month)
2005-02-28
date(now() + interval 7 day + interval + 1 month)
2005-02-28
date(now() + interval 8 day + interval + 1 month)
2005-03-01
date(now() + interval 9 day + interval + 1 month)
2005-03-02
------
------
date(now() + interval 2 day + interval + 1 month - interval 1 year)
2004-02-26
date(now() + interval 3 day + interval + 1 month - interval 1 year)
2004-02-27
date(now() + interval 4 day + interval + 1 month - interval 1 year)
2004-02-28
date(now() + interval 5 day + interval + 1 month - interval 1 year)
2004-02-28
date(now() + interval 6 day + interval + 1 month - interval 1 year)
2004-02-28
date(now() + interval 7 day + interval + 1 month - interval 1 year)
2004-02-28
date(now() + interval 8 day + interval + 1 month - interval 1 year)
2004-03-01
date(now() + interval 9 day + interval + 1 month - interval 1 year)
2004-03-02

Suggested fix:
No idea . ;-)
[24 Jan 2005 15:40] Aleksey Kishkin
tested on 4.1.9-standard on slackware linux 10 and windows xp sp2.
[27 May 2005 23:30] Jim Winstead
This is the expected behavior, as near as I can tell. (You forgot one of the vital components of a bug report: what you expected the output to be.)

From the manual (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html):

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'