MySQL Bugs: #16226: TIMESTAMPDIFF seems to ignore time when interval is bigger than week

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

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.

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)

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

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.

Very nice patch and test case.

Pushed to mysql-5.0.maint

Pushed to 5.0.25

Noted in 5.0.25 changelog.

TIMESTAMPDIFF() examined only the date and ignored the time when the
requested difference unit was months or quarters.

Merged into 5.1.12

Noted in 5.1.12 changelog.