Thu Feb 21 19:12:08 CET 2008

mysql time datatype inconsistancies

A surprising behavior of a migrated app recently caught my attention, and it lead to the observation of mysqld behaving inconsitant on time datatypes: different version, different behavior, i.e. the newer the version, the stronger it will enforce datatype limits.
As this took a couple of minutes to find and I did not really find many notes about it, I'll go ahead and write it down in a quick manner..

First of all, enforcing the documented limit is of course fine, as it resembles a bugfix. But i guess this wasn't the only application depending erroneously on the fact that time-datatype functions actually used to return the expected time rather than the truncated one - especially since the legal values are astonishingly low.

One might expect, for example, the following statement:

mysql> select timediff('2008-01-01 03:13:37', '2007-01-01 03:13:37');
| timediff('2008-01-01 03:13:37', '2007-01-01 03:13:37') |
| 8760:00:00                                             |
1 row in set (0.00 sec)

This used to work exacly like this in mysql 4 - but it was a not too obvious bug, as mysql states for version 4 as well as for version 5 that TIME values may only range from '-838:59:59' to '838:59:59'.

Consequently, in newer versions the result of the above statement will be 838:59:59. That's right, maximum is 839 hours. Timediff() is therefore rendered quite unusable for calculating the difference between two dates.
In mysqld 4 however, TIME values were only truncated to 2^31 hours, which is enough for almost all time operations one would use it for - timediff() being just one example of many.

Now some of these were fixed to always strip down to 839 hours (and creating a warning), some were fixed later, for example

mysql> select cast("1337:01:01" as time) as t;
| t          |
| 1337:01:01 |

still works just like this in some versions of mysqld 5.0 - the same behaviour known from 4.1. In current versions all TIME values are truncated, at least the ones previously known to be flawed (see bug #20292), I could not reproduce any time-out of bounds in really up to date versions, either.

Posted by iso | Permanent Link | Tags: code, mysql, annoyance, linux | comments >>