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.