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: | |
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
[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.