Bug #16226 TIMESTAMPDIFF seems to ignore time when interval is bigger than week
Submitted: 5 Jan 2006 15:00 Modified: 15 Aug 2006 3:23
Reporter: Justas Vilimas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19-BK, 5.0.18 OS:Linux (Linux)
Assigned to: Ian Greenhoe CPU Architecture:Any

[5 Jan 2006 15:00] Justas Vilimas
Description:
TIMESTAMPDIFF function returns interval based on two `times` like:

--- cut ---
mysql> SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29') |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27') |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
--- cut ---

but when it comes to bigger intervals like "month" or "year" then time seems to be ignored and the same result is returned, like:
--- cut ---
mysql> SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');
+------------------------------------------------------------------+
| TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29') |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
+------------------------------------------------------------------+
| TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27') |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)
--- cut ---

How to repeat:
install mysql 5.0.18 and run query:
SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
result of this query should return 0(zero), but returns 1(one) instead.

same with "year" interval:
SELECT TIMESTAMPDIFF(year,'2005-01-10 14:30:28','2006-01-10 14:30:27');
this one also returns 1(one), but should 0(zero).

Suggested fix:
Not known to me.
[6 Jan 2006 14:34] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.19-BK (ChangeSet@1.2010.1.2, 2006-01-04 21:39:39+03:00) on Linux:

mysql> SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');
+------------------------------------------------------------------+
| TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27') |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT TIMESTAMPDIFF(year,'2005-01-10 14:30:28','2006-01-10 14:30:27');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(year,'2005-01-10 14:30:28','2006-01-10 14:30:27') |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

But:

mysql> SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27') |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');
+-----------------------------------------------------------------+
| TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28') |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)
[23 Jun 2006 5:38] 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/commits/8121
[29 Jul 2006 3:52] 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/commits/9752

ChangeSet@1.2241, 2006-07-28 20:51:17-07:00, igreenhoe@anubis.greendragongames.com +3 -0
  Fix for bug #16226 (timestamp_diff truncation issue when requesting
  difference between timestamp in values of months and quarters.)
  
  Problem:  when requesting timestamp diff in months or quarters, it
  would only examine the date (and not the time) for the comparison.
  
  Solution:  increased precision of comparison.
[31 Jul 2006 11:17] Magnus Blåudd
Very nice patch and test case.
[1 Aug 2006 13:18] Magnus Blåudd
Pushed to mysql-5.0.maint
[3 Aug 2006 16:41] Magnus Blåudd
Pushed to 5.0.25
[4 Aug 2006 17:23] Paul Dubois
Noted in 5.0.25 changelog.

TIMESTAMPDIFF() examined only the date and ignored the time when the
requested difference unit was months or quarters.
[14 Aug 2006 21:26] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 3:23] Paul Dubois
Noted in 5.1.12 changelog.