Bug #37106 SELECT (datetimeB - datetimeA)
Submitted: 30 May 2008 16:43 Modified: 31 May 2008 17:05
Reporter: Santiago Trebucq Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (Debian_0ubuntu6.06.9)
Assigned to: CPU Architecture:Any
Tags: SUBSTRACT DATETIME in SELECT CLAUSE

[30 May 2008 16:43] Santiago Trebucq 
Description:
When execute :
SELECT MyColumnDateTime2, MyColumnDateTime1, (MyColumnDateTime2 - MyColumnDateTime1) as MyDateDiff 
FROM      TableX T;

In MyDateDiff I have SECONDS (in numeric format) of difference between MyColumnDateTime2 and MyColumnDateTime1.

If only diferences exists in SECONDS, then result is OK, but when the diferences are present in MINUTES and SECONDS the result is WRONG.

Example:
MyColumnDateTime2    MyColumnDateTime1    MyDateDiff
2008-05-29 12:00:27  2008-05-29 12:00:16  11.000000   -> Ok
2008-05-29 12:00:48  2008-05-29 12:00:35  13.000000   -> Ok
2008-05-29 12:01:22  2008-05-29 12:00:36  86.000000   -> WRONG!
2008-05-29 12:01:01  2008-05-29 12:00:47  54.000000   -> WRONG!
2008-05-29 12:01:14  2008-05-29 12:01:01  13.000000   -> Ok
2008-05-29 12:01:34  2008-05-29 12:01:11  23.000000   -> Ok
2008-05-29 12:01:40  2008-05-29 12:01:18  22.000000   -> Ok
2008-05-29 12:01:57  2008-05-29 12:01:42  15.000000   -> Ok
2008-05-29 12:02:07  2008-05-29 12:01:53  54.000000   -> WRONG!
2008-05-29 12:02:13  2008-05-29 12:01:54  59.000000   -> WRONG!
2008-05-29 12:02:26  2008-05-29 12:02:11  15.000000   -> Ok
2008-05-29 12:02:48  2008-05-29 12:02:24  24.000000   -> Ok
2008-05-29 12:02:51  2008-05-29 12:02:39  12.000000   -> Ok
2008-05-29 12:03:35  2008-05-29 12:03:05  30.000000   -> Ok
2008-05-29 12:03:31  2008-05-29 12:03:15  16.000000   -> Ok
2008-05-29 12:04:12  2008-05-29 12:03:31  81.000000   -> WRONG!
2008-05-29 12:03:49  2008-05-29 12:03:35  14.000000   -> Ok
2008-05-29 12:04:06  2008-05-29 12:03:54  52.000000   -> WRONG!

How to repeat:
Simply add some DATETIME values into table and execute a SELECT statement.

Suggested fix:
TIMEDIFF work well.
[31 May 2008 17:05] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

When you substract DATETIME values using operator '-' DATETIME values are converted to double hence the result. Please use functiion TIMEDIFF instead http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff