为什么MySQL的最大时限为838:59:59?
我自己已经遇到了限制，但是尽管在线上有很多闲聊，但我从未见过为什么 TIME数据类型的上限和下限是什么的解释.官方参考位于 http://dev.mysql.com/doc/refman /5.7/en/time.html 说
I've run into the limit myself, but despite lots of chatter online, I've never seen an explanation for why the upper and lower limit for the TIME data type is what it is. The official reference at http://dev.mysql.com/doc/refman/5.7/en/time.html says
TIME值的范围可以从"-838:59:59"到"838:59:59".小时部分可能是如此之大，因为时间"类型不仅可以用来表示一天中的某个时间(必须少于24小时)，而且可以用来表示经过的时间或两个事件之间的时间间隔(可能远大于24小时，甚至是负面的.
TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be so large because the TIME type 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).
但是我不知道为什么不允许小时数如此之大，而是为什么它会被截断.对于几天而言，这么多小时似乎没有任何意义，或者如果我尝试想象将整数存储多少秒的可能截止时间，则没有任何意义.那为什么范围呢?
But I'm wondering not why the hours part is allowed to be "so large", but why it's cut off where it is. There doesn't seem to be any significance to that many hours in regards to days, or if I try to imagine possible cutoffs for how many seconds could be stored as an integer. So why the range?
TIME值始终存储在MySQL中的3个字节上.但是格式在版本5.6.4上更改了.我怀疑这不是第一次改变.但是另一种变化(如果有的话)是很久以前发生的，并且没有公开的证据. GitHub上的MySQL源代码历史记录从5.5版开始(最早的提交是在2008年5月)，但是我要查找的更改发生在2001-2002年左右(MySQL 4在2003年发布)
The 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 (MySQL 4 was launched in 2003)
如文档中所述，当前格式使用6位(秒)(可能的值:0至63)，6位(分钟)，10位(小时)(可能的值:0至
The current format, as described in the documentation, uses 6 bits for seconds (possible values: 0 to 63), 6 bits for minutes, 10 bits for hours (possible values: 0 to 1023), 1 bit for sign (add the negative values of the already mentioned intervals) and 1 bit is unused and labelled "reserved for future extensions".
它经过优化，可以处理时间成分(小时，分钟，秒)，并且不会浪费太多空间.使用这种格式，可以在-1023:59:59和+1023:59:59之间存储值.但是，MySQL将小时数限制为838，可能是为了与前一阵子编写的应用程序向后兼容，而我认为这是极限.
It is optimized for working with time components (hours, minutes, seconds) and doesn't waste much space. Using this format it's possible to store values between -1023:59:59 and +1023:59:59. However MySQL limits the number of hours to 838, probably for backward compatibility with applications that were written a while ago, when I think this was the limit.
直到版本5.6.4，TIME值也存储在3个字节上，并且组件打包为days * 24 * 3600 + hours * 3600 + minutes * 60 + seconds.该格式已针对使用时间戳进行了优化(因为实际上是时间戳).使用这种格式，可以将值存储在大约-2330至+2330小时的范围内.尽管有大量可用的值，但MySQL仍将值限制为-838至+838小时.
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 -2330 to +2330 hours. While having this big range of values available, MySQL was still limiting the values to -838 to +838 hours.
在MySQL 4上存在错误#11655 .使用嵌套的SELECT语句在-838..+838范围之外的TIME值.它不是一个功能，而是一个错误，并且已得到修复.
There was bug #11655 on MySQL 4. It was possible to return TIME values outside the -838..+838 range using nested SELECT statements. It was not a feature but a bug and it was fixed.
向后兼容.将值限制在此范围内并主动更改任何产生TIME值的代码的唯一原因是
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.
我怀疑MySQL 3使用了另一种格式，由于数据打包的方式，其有效值限制在-838..+838小时范围内.
I suspect MySQL 3 used a different format that, due to the way the data was packed, limited the valid values to the range -838..+838 hours.
通过查看当前的 MySQL的源代码我发现了一个有趣的公式:
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)
暂时暂时忽略上面使用的名称中的 MAX 部分，让我们仅记住TIME_MAX_MINUTE和TIME_MAX_SECOND是00和59之间的数字.该公式仅将小时，分钟和秒连接成一个整数.例如，值170:29:45变为1702945.
Let's ignore for the moment the MAX part of the names used above and let's remember only that TIME_MAX_MINUTE and TIME_MAX_SECOND are numbers between 00 and 59. The formula just concatenates the hours, minutes and seconds in a single integer number. For example, the value 170:29:45 becomes 1702945.
此公式引发了以下问题:假设TIME值存储在带符号的3个字节上，用这种方式可以表示的最大正值是多少?
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?
我们要查找的值是0x7FFFFF，十进制表示法是8388607.由于最后四位数字(8607)应该读为分钟(86)和秒(07)，并且它们的最大有效值为59，因此可以使用符号将3个字节中的最大值存储为上面的公式是8385959. TIME是+838:59:59.塔达！
The value we are looking for is 0x7FFFFF that in decimal notation is 8388607. Since the last four digits (8607) should be read as minutes (86) and seconds (07) and their maximum valid values is 59, the greatest value that can be stored on 3 bytes with sign using the formula above is 8385959. Which, as TIME is +838:59:59. Ta-da!
猜猜是什么?上面列出的C代码片段是从中提取的:
Guess what? The fragment of C code listed above was extracted from this:
/* Limits for the TIME data type */
#define TIME_MAX_HOUR 838
#define TIME_MAX_MINUTE 59
#define TIME_MAX_SECOND 59
#define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + TIME_MAX_SECOND)
我确信这是MySQL 3用来内部保留TIME值的方式.这种格式强加了范围的限制，而对后续版本的向后兼容性要求将这种限制传播到了我们的时代.
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.