Posts Tagged ‘datetime’

Don’t try to do date math in MySQL

Monday, January 14th, 2013

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.