为什么 MySQL unix 时间会超出 32 位无符号整数限制？

发布于 2024-10-12 23:10:19 字数 925 浏览 6 评论 0原文

mysql> SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+
| 2037-12-31 18:59:59       | NULL                      |    2147483647 |                   -1566850 | 
+---------------------------+---------------------------+---------------+----------------------------+
1 row in set (0.00 sec)

mysql>

第一个字段是我可以赋予 FROM_UNIXTIME 的最高可能值。下一个字段是该值加一，返回NULL。第三个字段是无符号 32 位 int 的最大可能值。最终值是最高可能的 UNIXTIME 和最高可能的 int 之间的差值，略高于 18 天的秒数。它似乎在当地时区的 2037 末尾停止。有什么想法吗？这是其中一项计算中的自然断点吗？这只是 mysqld 中的任意限制吗？

原文

mysql> SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+
| 2037-12-31 18:59:59       | NULL                      |    2147483647 |                   -1566850 | 
+---------------------------+---------------------------+---------------+----------------------------+
1 row in set (0.00 sec)

mysql>

The first field is the highest possible value I can give to FROM_UNIXTIME. The next field is that value plus one which returns NULL. The third field is the highest possible value for an unsigned 32 bit int. The final value is the difference between the highest possible UNIXTIME and the highest possible int which is a little over 18 days worth of seconds. It appears that it stops at the end of 2037 in the local timezone. Any ideas why? Is that a natural breaking point in one of the calculations? Is that just an arbitrary limit in mysqld?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

零度℉ 2024-10-19 23:10:19

通常unix时间戳范围是从1970年1月1日到2037年12月31日
有关更多信息，请查看http://en.wikipedia.org/wiki/Year_2038_problem

回复收藏 0 原文

半衬遮猫 2024-10-19 23:10:19

我在 GMT+0200 得到了截然不同的结果。 i686 和 x86_64 的结果相同。

大概 2038-01-01 UTC 是不允许的。

SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+ 
| 2038-01-01 01:59:59       | 2038-01-01 02:00:00       |    2147483647 |                    -1566850 |  
+---------------------------+---------------------------+---------------+------------------- ---------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

I got very different results in GMT+0200. same results for both i686 and x86_64.

Propably 2038-01-01 UTC was not allowed.

SELECT FROM_UNIXTIME(2145916799), FROM_UNIXTIME(2145916800), POW(2,32-1)-1, 2145916799 - POW(2,32-1)-1;
+---------------------------+---------------------------+---------------+----------------------------+
| FROM_UNIXTIME(2145916799) | FROM_UNIXTIME(2145916800) | POW(2,32-1)-1 | 2145916799 - POW(2,32-1)-1 |
+---------------------------+---------------------------+---------------+----------------------------+ 
| 2038-01-01 01:59:59       | 2038-01-01 02:00:00       |    2147483647 |                    -1566850 |  
+---------------------------+---------------------------+---------------+------------------- ---------+
1 row in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

回复收藏 0 原文

~没有更多了~