An interesting question from Stack Overflow asks “Why is MySQL’s maximum time limit 838:59:59?”
The official reference at http://dev.mysql.com/doc/refman/5.7/en/time.html says
TIMEvalues may range from
838:59:59. The hours part may be so large because the
TIMEtype can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).
TIME values were always stored on 3 bytes in MySQL. But the format changed on version 5.6.4.
I suspect this was not the first time when it changed. But the other change, if there was one, happened long time ago and there is no public evidence of it. The MySQL source code history on GitHub starts with version 5.5 (the oldest commit is from May 2008) but the change I am looking for happened somewhere around 2001-2002. The first stable release of MySQL 4 (version 4.0.4) was launched in September 2002.
TIME values are stored since version 5.6.4
The current format, as described in the documentation, uses the 24 bits as follows:
- 6 bits for seconds (possible values:
- 6 bits for minutes (again, possible values:
- 10 bits for hours (possible values:
- 1 bit for sign;
- 1 bit is unused and labelled “reserved for future extensions”.
It is optimized for working with time components (hours, minutes, seconds) and it doesn’t waste much space.
Using this format it’s possible to store values between
However MySQL limits the number of hours to
838, probably for backward compatibility with applications
that were written a while ago, when I think
838 hours was the upper limit.
The format of
TIME before version 5.6.4
Until version 5.6.4, the
TIME values were also stored on 3 bytes and the components were packed as
days * 24 * 3600 + hours * 3600 + minutes * 60 + seconds.
This format was optimized for working with timestamps (because it was, in fact, a timestamp). Using this format it would be possible to store values in the range of about
I think this format was introduced in MySQL 4.
While having this big range of values available, MySQL was still limiting the values to
for compatibility with applications developed on MySQL 3.
The format of
TIME before MySQL 4
There was bug #11655 on MySQL 4. It was possible to return
TIME values outside the
range using nested
SELECT statements. It was not a feature but a bug and it was fixed.
The only reason to limit the values to this range and to actively change any piece of code that produces
TIME values outside it was backward compatibility.
I suspect MySQL 3 used a different format that, due to the way the data was packed,
limited the valid values to the range
By looking into the current MySQL’s source code I found this interesting formula:
#define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + TIME_MAX_SECOND)
Let’s ignore for the moment the
MAX part of the names used above and let’s remember only that
TIME_MAX_SECOND are numbers between
The formula just concatenates the hours, minutes and seconds in a single integer number.
For example, the value
This formula raises the following question: given that the
TIME values are stored on 3 bytes with sign,
what is the maximum positive value that can be represented this way?
The maximum value that can be stored on 3 digits with sign is
0x7FFFFF that in decimal notation is
Since the last four digits (
8607) should be read as minutes (
86) and seconds (
07) and their maximum
valid value is
59, the greatest value that can be stored on 3 bytes with sign using the formula above is
Guess what? The fragment of
C code listed above was extracted from:
90 91 92 93 94
Now I am sure this is how MySQL 3 used to keep the
TIME values internally. This format imposed the limitation
of the range, and the backward compatibility requirement on the subsequent versions propagated
the limitation to our days.