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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:ALL OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[7 Jan 2016 12:43] Su Dylan
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)

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

In MySQL 5.7, timestamp is a DOUBLE rather than BIGINT because its value includes a microseconds part.

How to repeat:
SET TIMESTAMP = 2147483647;

Suggested fix:
"SET TIMESTAMP = 2147483647" should succeed.
[7 Jan 2016 16:00] Peter Laursen
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
[7 Jan 2016 16:08] MySQL Verification Team
another similar case: https://bugs.mysql.com/bug.php?id=12654 ?
[8 Jan 2016 2:23] Su Dylan
Year 2038 is really close now.
[8 Jan 2016 5:45] MySQL Verification Team
Thank you for the report.
[9 Dec 2019 20:54] Roy Lyseng
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.
[9 Dec 2019 23:46] Jon Stephens
The timestamp system variable doesn't show its range. Add this info.
[10 Dec 2019 0:17] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.
[10 Dec 2019 0:18] Jon Stephens
Fixed in all versions of the Manual 5.5+ by mysqldoc rev 64460. Closed.