MySQL Bugs: #31864: TIMEDIFF throwing warnings

Bug #31864	TIMEDIFF throwing warnings
Submitted:	25 Oct 2007 21:24	Modified:	8 Feb 2008 2:13
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.1.22	OS:	Windows (Any)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	hour, qc, timediff, warning

Description:
Hi,

It seems that when TimeDiff is performed on 2 dates that exceed 36 days that a warning is thrown, and the resulting value is always capped to 838.

How to repeat:
mysql> SELECT TIMEDIFF('2000/02/05 23:00:00','2000/01/01 00:00:00') AS Ex1;
+-----------+
| Ex1       |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '863:00:00' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Remove the restraint that is preventing correct rsults from being return, and remove warning as well.

+corrected version

regression: works fine with 4.1, fails with 5.0 and 5.1

mysqltest test case

Attachment: bug31864.tgz (application/x-gtar, text), 834 bytes.

Would like better defect class on this bug.

This may be a duplicate http://bugs.mysql.com/bug.php?id=31990

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff) the TIMEDIFF function return a TIME value.
TIME values are restricted to +/- 838:59:59 (http://dev.mysql.com/doc/refman/5.1/en/time.html).
You can use TIMESTAMPDIFF function instead.

Thank you Evgeny, 5.1 documentation is quite an improvement.