MySQL TIMESTAMP类型范围矛盾及实现细节技术问询

问题背景 #

MySQL官方文档明确TIMESTAMP类型的存储取值范围为UTC时间'1970-01-01 00:00:01.000000'至'2038-01-19 03:14:07.499999'，但执行以下SQL语句却能正常返回结果：

SELECT TIMESTAMP('1970-01-01 00:00:00.000000', 0.00001) FROM DUAL;

返回结果为'1970-01-01 00:00:00.000010'，该值超出了文档规定的TIMESTAMP存储范围。

咨询的核心问题 #

• TIMESTAMP表示的是当前时间与UTC时间'1970-01-01 00:00:00.000000'的差值，这个差值的单位是秒还是毫秒？
• TIMESTAMP类型的底层实现是否基于BIGINT（或long）？

问题解答 #

为什么会出现超出范围的结果 #

你用的TIMESTAMP()是MySQL的日期时间函数，不是TIMESTAMP类型的存储操作。这个函数的作用是把输入的日期时间加上指定的时间间隔，返回的是字符串格式的时间值，不受TIMESTAMP类型存储范围的限制。

如果试着把这个超出范围的值插入到TIMESTAMP类型的字段里，就会触发预期的范围错误，比如执行：

CREATE TABLE test_ts (ts TIMESTAMP);
INSERT INTO test_ts VALUES ('1970-01-01 00:00:00.000010');

会得到Out of range value for column 'ts' at row 1的报错，这才符合文档中规定的存储范围限制。

差值单位与底层实现细节 #

1. 差值单位：TIMESTAMP类型存储的是从UTC时间'1970-01-01 00:00:00'开始的秒数差值。从MySQL 5.6.4版本开始支持微秒精度，此时会把微秒部分以“秒的小数部分”形式存储（比如0.000010秒就是10微秒）。
2. 底层实现：TIMESTAMP的底层存储不是基于BIGINT，而是用4字节的有符号INT存储秒数差值——这也是2038年问题的根源：4字节有符号INT的最大值对应到UTC时间就是2038-01-19 03:14:07。如果是带微秒精度的TIMESTAMP，会额外用1-3字节存储微秒部分，整体并非BIGINT类型。

内容的提问来源于stack exchange，提问作者HuaWang135608