rlucas.net: The Next Generation Rotating Header Image

January 14th, 2013:

Don’t try to do date math in MySQL

MySQL (5.5) has incredibly broken date math semantics.

Subtracting two “datetime” types gives you a number which is manifestly not the number of seconds between those datetimes. (It’s also not ~ 10^n times that number for any n.)

So naive subtraction is broken. What about “timediff?” Well, you can use that function to compare two datetimes, and what it returns you is in the form “HH:MM:SS.” Nice. Seems to be some kind of useful interval type, right? If you decide you just want to do some averages on this result, you can then use the “time_to_sec” function, and now you’re computing some nice figures like the average time to click on an email.

…which, in keeping with the MySQL philosophy, sort of works until it (almost) silently stops working. What I mean is that the first time your date math gives you a figure like “883:15:19,” the “time_to_sec” function will start quietly giving warnings. If you turn on warnings (which really REALLY need to be on by default, here, MySQL folks), you will learn something like:

Warning (Code 1292): Truncated incorrect time value: ‘883:15:39’

Oh, great. There’s some number of hours one can put into a time value which is subsequently converted into seconds. What that number is, who knows. (Apparently not the author of the “timediff” function, because that’s what gave me that incorrect time value.)

Just give up and convert everything to an epoch timestamp. After all, who needs milliseconds anyhow? Other solutions include: use Postgres.

MySQL silently ignores aggregate query errors by default

In a SQL query, if you use aggregate functions (min, max, count, sum, etc.) and mix them with non-aggregate columns, you have to indicate how to “group” things. Otherwise, the output is not predictable.

MySQL by default will just ignore these problems and make up something. This can make bugs in complex queries hard to track down (and it virtually guarantees that a novice or dullard will slip some errors into such queries eventually).

You can fix this with:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,’,ONLY_FULL_GROUP_BY’));

(That is, you want the “ONLY_FULL_GROUP_BY” option set. The SET above can be run in the mysql> prompt and affects that session only; thinking DBAs should strongly consider enforcing this as a server option.)

I am too tired and busy to give in to the temptation to unleash a rant about MySQL here, but PLEEEEZ. It’s the year 2013 and this is still an issue??

Hat tip Michael McLaughlin: http://blog.mclaughlinsoftware.com/2010/03/10/mysql-standard-group-by/

MySQL docs on this “extension” http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html