Bug #13534 function error
Submitted: 27 Sep 2005 18:47 Modified: 27 Oct 2005 2:50
Reporter: Janno Rütter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:Linux (linux)
Assigned to: Jim Winstead CPU Architecture:Any

[27 Sep 2005 18:47] Janno Rütter 
Description:
TIMESTAMPDIFF Not working correctly with years 2000,2004

How to repeat:
mysql> select TIMESTAMPDIFF(MONTH,'2004-09-19','2005-09-19');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2004-09-19','2005-09-19') |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select TIMESTAMPDIFF(MONTH,'2004-08-19','2005-09-19');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2004-08-19','2005-09-19') |
+------------------------------------------------+
|                                             12 |
+------------------------------------------------+
1 row in set (0.00 sec)
[27 Sep 2005 21:53] Hartmut Holzgraefe 
looks like an off-by-one bug when both:

TIMESTAMPDIFF(MONTH,'2004-09-11','2004-09-11') => 0, OK
TIMESTAMPDIFF(MONTH,'2004-09-11','2005-09-11') => 0, expected 12
TIMESTAMPDIFF(MONTH,'2004-09-11','2006-09-11') => 12, expected 24
TIMESTAMPDIFF(MONTH,'2004-09-11','2007-09-11') => 24, expected 36
TIMESTAMPDIFF(MONTH,'2005-09-11','2004-09-11') => 0, expected -12
TIMESTAMPDIFF(MONTH,'2005-09-11','2003-09-11') => -24, OK
[27 Sep 2005 22:13] Hartmut Holzgraefe 
looking at it again it's actually a bug in the way leap years are handled here,
if both arguments are non-leap years the results are ok, same if both are
leap years, even a leap and a none-leap year are ok if the date is less or 
equal to february 28th

just with one argument being a leap year but not the other and a date
in march or later makes the function fail
[7 Oct 2005 17:45] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30826
[13 Oct 2005 22:31] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31077
[25 Oct 2005 22:52] Jim Winstead 
Fixed in 5.0.16.
[27 Oct 2005 2:50] Paul DuBois 
Noted in 5.0.16 changelog.