MySQL Bugs: #52693: Cannot insert the maximum timestamp value into a timestamp column

Bug #52693	Cannot insert the maximum timestamp value into a timestamp column
Submitted:	8 Apr 2010 15:11	Modified:	8 Apr 2010 20:53
Reporter:	Tom Groszko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	maximum timestamp

Description:
MySQL 5.1 Reference Manual
A timestamp. The range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
The largest range I can insert is something less than '2038-01-19 03:14:07' namely '2038-01-18 22:14:07'.

This could be a bug in the documentation, a bug in the code or a bug in the way I am reading the documentation. I am just learning my way around MySQL.

USE MTGTest;
DROP TABLE MAXTIMESTAMP;
CREATE TABLE MAXTIMESTAMP
(    Max_TIMESTAMP  TIMESTAMP
);
INSERT INTO `mtgtest`.`maxtimestamp` (`Max_TIMESTAMP`) VALUES ('2038-01-18 22:14:07');
INSERT INTO `mtgtest`.`maxtimestamp` (`Max_TIMESTAMP`) VALUES ('2038-01-18 22:14:08');
SELECT * FROM `mtgtest`.`maxtimestamp`;

How to repeat:
USE MTGTest;
DROP TABLE MAXTIMESTAMP;
CREATE TABLE MAXTIMESTAMP
(    Max_TIMESTAMP  TIMESTAMP
);
INSERT INTO `mtgtest`.`maxtimestamp` (`Max_TIMESTAMP`) VALUES ('2038-01-18 22:14:07');
INSERT INTO `mtgtest`.`maxtimestamp` (`Max_TIMESTAMP`) VALUES ('2038-01-18 22:14:08');
SELECT * FROM `mtgtest`.`maxtimestamp`;

Thank you for the report.

Please provide output of SHOW VARIABLES LIKE 'time_zone' and if this is SYSTEM timezone indicate which timezone uses your OS.

SHOW VARIABLES LIKE 'time_zone' ;
time_zone SYSTEM

I am not sure what you mean by "indicate which timezone uses your OS".
What command do I issue to show this?

I am in the Eastern United States timezone which is currently using daylight savings time. The documentation for this value does not seem to care where in the world I am. It gives two unqualified values of which only the first works.

Thank you for the feedback.

Eastern United States timezone is not UTC. See http://dev.mysql.com/doc/refman/5.1/en/timestamp.html about how TIMESTAMP columns are affected by timezones. So this is not a bug.

Thanks that clears it up.