Using CURDATE() the wrong way

It has been a while since I wrote on this blog. Basically I had too much on my mind (expanding my department, holidays, etc) to actually write here and I’ll promise to post more regularly from now onwards. ;)

Anyway, as the title already suggests: I found out how you can use CURDATE() in a wrong way. One of the developers in my company asked me to help him out as his code all of a sudden did not work properly anymore. Or even better: it used to process several thousands of rows and all of a sudden it processed none.

I looked at his code snippet and it was quite a long query with a lot of brackets:
SELECT SUM(some_count_col), logdate, loghour FROM logs
WHERE (logdate = CURDATE() AND loghour = HOUR(NOW())
GROUP BY logdate, loghour;

Column wise logdate is of the type DATE and loghour of the type TINYINT.note that this is, obviously, not the original query, but it is similar

Apart from the fact that his usage of brackets makes the query quite unreadable I was quickly able to simplify the query to this:
SELECT SUM(some_count_col), logdate, loghour FROM logs
WHERE (logdate BETWEEN CURDATE() AND (CURDATE()-2)
OR (logdate = CURDATE()-3 AND loghour >= HOUR(NOW())
GROUP BY logdate, loghour;

There is no such thing as storing logs in the future, right? But yet still I wasn’t able to help him out properly as it still did not return any rows.

Then it struck me: he wasn’t using either the DATE_SUB() function or “INTERVAL 3 day” to calculate the date, he was actually substracting a number from the CURDATE() function. So I looked up the CURDATE() function in the MySQL documentation:

CURDATE()
Returns the current date as a value in ‘YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

That’s weird: you actually can use CURDATE() in numeric context and adding or substracting is valid…

So let’s see what happens if you make calculations with it:
[(none)]> SELECT CURDATE()-4;
+-------------+
| CURDATE()-4 |
+-------------+
| 20121025 |
+-------------+
1 row in set (0.00 sec)

Makes perfectly sense for todays date, right?

Well actually on that particular day I helped out the developer it was the first of October and the output was the following:
[(none)]> SELECT CURDATE()-4;
+-------------+
| CURDATE()-4 |
+-------------+
| 20120997 |
+-------------+
1 row in set (0.00 sec)

Doing it for today with +4 would also give a similar behavior with the date 20121033.

I can imagine there is a need for CURDATE() to output both string and numeric context. However given that the MySQL documentation contains an example where a calculation is performed on CURDATE() to output it in numeric context makes it only more confusing for users.

I am aware that developers should always check their SQL syntax and see if it actually outputs warnings/errors, but this particular case does work for 24/26/27 days each month. It would be great to see a warning in the documentation to only perform date calculations in the proper way.