Bug #2124 current_date - somedate gives useless results?
Submitted: 15 Dec 2003 14:19 Modified: 16 Dec 2003 11:52
Reporter: Arjen lastname Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.14 OS:Linux (Linux)
Assigned to: Indrek Siitan CPU Architecture:Any

[15 Dec 2003 14:19] Arjen lastname
Description:
When you'd like to use some substract-stuff, like current_date - somedate, you get really weird results...

It can be:
The number of days.
The (number of months + the fraction of months) * 100
The (number of years + fraction of years) * 10000
Some other weird numbers

But these results are totally useless if you'd want to calculate with those...
In my application, I just devide them by 10000 in the hope they won't be in the way if its lower than one year, but its not a very nice solution.

How to repeat:
mysql> select datum, current_date, current_date - datum from datums;
+------------+--------------+----------------------+
| datum      | current_date | current_date - datum |
+------------+--------------+----------------------+
| 2003-01-01 | 2003-12-15   |                 1114 |
| 2003-12-01 | 2003-12-15   |                   14 |
| 2002-12-01 | 2003-12-15   |                10014 |
| 1970-01-01 | 2003-12-15   |               331114 |
| 2003-12-16 | 2003-12-15   |                   -1 |
| 2002-12-16 | 2003-12-15   |                 9999 |
+------------+--------------+----------------------+
6 rows in set (0.00 sec)

mysql> desc datums;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| datum | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

Suggested fix:
Perhaps adding an interval-type would solve this. Although the SQL-spec defines to return the number of days (as an integer) with such an substraction.

The results should at least be logical and useful in calculations.
If something is 14 days old, and something else is 1 year and 14 days old, the difference in the substraction should not be 10000, but 365 (or 366 in case of a leap year).
[15 Dec 2003 16:21] Indrek Siitan
Currently, MySQL seems to try to do the best to convert those dates to integer
for subsctraction, so 2003-12-15 - 2003-01-01 really is performed as
20031215-20030101, which is 1114. Same holds true for other calculations.

I will check with Peter Gulutzan, our standards expert, and our lead developers
on what their view is on this, but meanwhile, "our" correct way to achieve what
you seem to want is to use the TO_DAYS() function. In your case, the query you
want would be:

  SELECT datum, current_date, to_days(current_date)-to_days(datum) 
  FROM datums;
[15 Dec 2003 16:39] Harrison Fisk
Also in MySQL 4.1.1 there is now the DATEDIFF() function which can do exactly what you want, subtract two dates and get the amount of days different between the two.
[16 Dec 2003 11:40] Arjen lastname
I was indeed able to work around all this using TO_DAYS and FROM_DAYS.

My main concern is that the substraction of two dates results in something that is not very easy to return to a date (or interval). Actually, additions and substractions on a linear scale should result in values on that same (or a similar) linear scale, MySQL doesn't do that, does it?
If I do 2003-12-30 - 2003-12-29 the result is 1, one day appearantly.
If I do 2003-12-30 - 2003-11-30 then there are 30 days in between. MySQL will return 100 however. That's not 100 days, it's 1 month (times 100).
Same story for 2003-12-30 - 2002-12-30. The result is 10000. Is that 100 months? Is that 10000 days? No its one year.

But afaik, every year has 12 months nowadays (the Romans might disagree), so if a result on a linear scale is 'one month', it should be something close to 12*'one month' for a year, shouldn't it?

Anyway, enough of that:
My main concern is that the result from the substraction isn't very useful, its the substraction of two integers which are build "as if they were a string" from the dates, and thereby loose most of the logic in it.

Afaik, your proposal: TO_DAYS(date1) - TO_DAYS(date2) is more-or-less what the standard specifies. (it specifies to return an interval, see ISO/IEC 9075-2:1999 (E) 4.7 Datetimes and intervals, page 25)
[16 Dec 2003 11:52] Indrek Siitan
Here's a comment from Peter Gulutzan:

  MySQL's "Date - Date" calculations are not
  standard SQL, but are rather like DB2's.

  DB2 returns "Date - Date" as a DECIMAL(8,0)
  number, in which the top 4 digits are year,
  the next 2 digits are month, the next 2 digits
  are month.

  Oracle says that "Date - Date" is a number of
  days, which is what the customer wants. Maybe
  if we ever support "sql_mode=Oracle" perfectly
  we'll do this, but I doubt that we ever will.

  Standard SQL would require an explicit CAST
  to INTERVAL DAY.

I'm going to mark this as "not a bug" for now, since it really is a gray area -
how to behave in situations not strictly set (or in this case, really allowed)
by the standard.