From: Travis Hillenbrand (travislouisville.edu)
Date: Mon Jul 04 2005 - 15:02:33 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

>Description:
        The number of months between two dates is not calculated properly by the
timestampdiff function when the second date is the same month in a future
year but the same day or earlier within the month.
        Specifically, the number of months is obtained by calculating the number of
complete years between the two dates, multiplying by 12, and adding the
number of months that exist in the remainder of the year that is left.
However, the loop that counts the months exits when the 'start' and 'end'
month are the same, regardless of whether these months are in different
years. In short, when the month of both dates are the same and the day of
the second date is less than or equal to that of the first date, the result
is just the number of years between the two dates multiplied by 12.

>How-To-Repeat:

This example works as expected, as the day component of the second date is
greater:
mysql> select timestampdiff(month,'2004-06-03 00:00:00','2005-06-04
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2005-06-04 00:00:00') |
+------------------------------------------------------------------+
| 12 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

This example works as expected, as the month differs between the two dates:
mysql> select timestampdiff(month,'2004-06-03 00:00:00','2005-05-31
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2005-05-31 00:00:00') |
+------------------------------------------------------------------+
| 11 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

These 4 examples demonstrate what happens when this is not the case:
mysql> select timestampdiff(month,'2004-06-03 00:00:00','2005-06-03
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2005-06-03 00:00:00') |
+------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampdiff(month,'2004-06-03 00:00:00','2005-06-02
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2005-06-02 00:00:00') |
+------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampdiff(month,'2004-06-03 00:00:00','2005-06-01
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2005-06-01 00:00:00') |
+------------------------------------------------------------------+
| 0 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select timestampdiff(month,'2004-06-03 00:00:00','2006-06-01
00:00:00');
+------------------------------------------------------------------+
| timestampdiff(month,'2004-06-03 00:00:00','2006-06-01 00:00:00') |
+------------------------------------------------------------------+
| 12 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

>Fix:
Applying the following patch corrects the problem for the system described
below:

--- sql/item_timefunc.cc~ Fri Jun 10 21:30:43 2005
+++ sql/item_timefunc.cc Sun Jul 3 16:26:19 2005
-2686,7 +2686,7
     month_end--;

     months= 12*diff_years;
- while (month_beg != month_end)
+ while (month_beg != month_end || year != year_end)
     {
       uint m_days= (uint) days_in_month[month_beg];
       if (month_beg == 1)

>Submitter-Id: Travis Hillenbrand
>Originator: Travis Hillenbrand
>Organization:
>MySQL support: none
>Synopsis: timestampdiff func miscalculation (month only)
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-5.0.7-beta (FreeBSD port: mysql-client-5.0.7)

>C compiler: 2.95.4
>C++ compiler: 2.95.4
>Environment:
System: FreeBSD GunSmoke 4.10-RELEASE-p16 FreeBSD 4.10-RELEASE-p16 #12: Sun
Jul 3 02:14:08 EDT 2005 rootGunSmoke:/usr/obj/usr/src/sys/GUNSMOKE
i386

Some paths: /bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.4 20020320 [FreeBSD]
Compilation info: CC='cc'
 CFLAGS='-O -pipe -march=pentiumpro -D_THREAD_SAFE' CXX='cc'
 CXXFLAGS=' -O -pipe -march=pentiumpro -D_THREAD_SAFE -O -pipe -march=pentiu
mpro -D_THREAD_SAFE -felide-constructors -fno-rtti -fno-exceptions'
LDFLAGS=' -L/usr/local/lib' ASFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1233782 May 14 07:04 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 May 14 07:04 /usr/lib/libc.so -> libc.so.4
lrwxr-xr-x 1 root wheel 18 May 2 2004 /usr/lib/libc.so.3 ->
/usr/lib/libc.so.4
-r--r--r-- 1 root wheel 590108 May 14 07:04 /usr/lib/libc.so.4
Configure command: ./configure '--localstatedir=/var/db/mysql'
'--without-debug' '--without-readline' '--without-libedit' '--without-bench'
'--without-extra-tools' '--with-libwrap' '--with-mysqlfs'
'--with-low-memory' '--with-comment=FreeBSD port: mysql-client-5.0.7'
'--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db'
'--with-named-thread-libs=-pthread' '--without-server' '--prefix=/usr/local'
'--build=i386-portbld-freebsd4.10' 'LDFLAGS= -L/usr/local/lib'
'CFLAGS=-O -pipe -march=pentiumpro -D_THREAD_SAFE'
'CPPFLAGS=-I/usr/local/include' 'CXX=cc'
'build_alias=i386-portbld-freebsd4.10' 'CC=cc'
'CXXFLAGS= -O -pipe -march=pentiumpro -D_THREAD_SAFE -O -pipe -march=pentium
pro -D_THREAD_SAFE -felide-constructors -fno-rtti -fno-exceptions'

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]