MySQL Bugs: #79872: Maximum value for system variable timestamp is 2147483647

Bug #79872	Maximum value for system variable timestamp is 2147483647
Submitted:	7 Jan 2016 12:43	Modified:	10 Dec 2019 0:17
Reporter:	Su Dylan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
Output:
=======
mysql> SET TIMESTAMP = 2147483647;
Query OK, 0 rows affected (0.00 sec)

mysql> SET TIMESTAMP = 2147483648;
ERROR 1231 (42000): Variable 'timestamp' can't be set to the value of '2147483648'

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
As document says, in 5.7, timestamp value should be a doulbe. It should be restricted to a signed int.

Quote:
=====
In MySQL 5.7, timestamp is a DOUBLE rather than BIGINT because its value includes a microseconds part.
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp

How to repeat:
SET TIMESTAMP = 2147483647;

Suggested fix:
"SET TIMESTAMP = 2147483647" should succeed.

This still applies in 5.7 "range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'" (according to http://dev.mysql.com/doc/refman/5.7/en/datetime.html).

From very old days MySQL TIMESTAMP was implemented just as an 'incarnation' of a (32 bit) unix_time, that has exactly the same range. And there are probably still much such limitation/dependency internally in the server code. However http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp dos not tell that it is a *DOUBLE with a limited range* what I think it should.

Besides as we are now as closing in to year 2038 (we are much closer to that now than to when unix_time started back in 1970) this should be lifted soon IMO. 

Hoever this is one of the 'skeletons in the cupboard' MySQL has. I have followed your reports here and you bumped into other ones as well!

-- Peter
-- not a MySQL/Oracle person

another similar case: https://bugs.mysql.com/bug.php?id=12654 ?

Year 2038 is really close now.

Thank you for the report.

Posted by developer:
 
This is not a bug.
Values for TIMESTAMP are limited to the range 1.0 .. 2147483647.0.
The low value corresponds to 1970-01-01 and the high value corresponds to 2038-01-19,
which is also the range for the TIMESTAMP data type.

The timestamp system variable doesn't show its range. Add this info.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Fixed in all versions of the Manual 5.5+ by mysqldoc rev 64460. Closed.