Bug #56326 Date Subtraction Math Error; Workaround With DATEDIFF()

• Submitted: 27 Aug 2010 15:11
• Modified: 27 Aug 2010 15:46
• Reporter: Jason McCollough
• Status: Not a Bug
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.1.41-3ubuntu12.6; Windows 5.1.41
• OS: Linux
• CPU Architecture: Any
• Tags: date, DATEDIFF, Subtraction

[27 Aug 2010 15:11] Jason McCollough

Description:
When calculating the difference in two date fields (where both days are not in the same month), the results are inaccurate using:

Method 1:
DATE2 - DATE1

But are accurate when using:

Method 2:
DATEDIFF(DATE2, DATE1)

This has resulted in inaccurate calculations of dates when using Method 1.  

How to repeat:
SELECT DATE('2010-07-01')-DATE('2010-06-30');
Expected: 1
Actual: 71.0

SELECT DATEDIFF(DATE('2010-07-01'), DATE('2010-06-30'));
Expected: 1
Actual: 1

SELECT DATE('2010-07-01')-DATE('2010-05-30');
Expected: 32
Actual: 171.0

SELECT DATEDIFF(DATE('2010-07-01'), DATE('2010-05-30'));
Expected: 32
Actual: 32

SELECT DATE('2010-07-01')-DATE('2009-07-01');
Expected: 365
Actual: 10000

SELECT DATEDIFF(DATE('2010-07-01'), DATE('2009-07-01'));
Expected: 365
Actual: 365

Suggested fix:
Fix the way date intervals are calculated by MySQL when using Method 1.

If the difference in behavior between method 1 and method 2 is expected or cannot be fixed for some reason, then it should be clearly documented.

[27 Aug 2010 15:46] Valeriy Kravchuk

I think this is explained at http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html and http://dev.mysql.com/doc/refman/5.1/en/arithmetic-functions.html. 

Manual never states that minus operator is implemented for DATE data type (it is arithmetic operator and apply to numbers, this is stated explicitly). So, dates are converted to numbers and then subtracted as numbers.