rlucas.net: The Next Generation Rotating Header Image

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.

2 Comments

  1. Will says:

    search pointed me here. see datediff which is different then timeidff

    e.g.
    mysql lunadb -NBe “select datediff(VisitDate,DateOfBirth)/365.25 as age from table

  2. rlucas says:

    Or instead of hardcoding your estimate of the earth’s axial precession and gravitational perturbations, while ignoring the Julian system for papering over those anomalies (considering, for example, that in a non-leap year your query would answer “false” to the question of whether a 365.1-day-old baby is 1.0 years old), you could just ask Postgres:

    db=> select age( ’2014-02-11′::date, ’2012-05-05′::date ) as age;
    age
    ———————-
    1 year 9 mons 6 days
    (1 row)

    db=> select age( ’2014-02-11′::date, ’2012-05-05′::date ) > ’1 year’::interval as old_enough;
    old_enough
    ————
    t
    (1 row)

Leave a Reply