//
you're reading...
MySQL syntax

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. ;)

Syntax error!

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.

mysql> SELECT CURDATE();
-> ‘2008-06-13′
mysql> SELECT CURDATE() + 0;
-> 20080613

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.

Obviously this statement works correctly if you change it to this:

[(none)]> SELECT CURDATE() - INTERVAL 4 DAY;
+----------------------------+
| CURDATE() - INTERVAL 4 DAY |
+----------------------------+
| 2012-10-25 |
+----------------------------+
1 row in set (0.00 sec)

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.

About these ads

About Art van Scheppingen

Head of Database Engineering at Spil Games

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 427 other followers

Follow

Get every new post delivered to your Inbox.

Join 427 other followers

%d bloggers like this: