MySQL Bugs: #36214: Timestamp column type limitation

Bug #36214	Timestamp column type limitation
Submitted:	19 Apr 2008 9:04	Modified:	25 Apr 2008 9:52
Reporter:	Shantanu Oak	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	timestamp, timezone

Description:
I can not insert any time from 00:00 to 05:30 for the date 1970-01-01 in a timestamp declared column. 
 
I think this has something to do with the timezone.
If someone is ahead of 5:30 hours than standard time, he can not set a timestamp value that is less than five and half hours on the first day of 1970.

How to repeat:
mysql> show create table mybug
********************* 1. row *********************
       Table: mybug
Create Table: CREATE TABLE `mybug` (
  `testtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> insert into mybug values ('1970-01-01 05:30:01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mybug values ('1970-01-01 05:29:01');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings\G
********************* 1. row *********************
  Level: Warning
   Code: 1264
Message: Out of range value adjusted for column 'testtime' at row 1

Suggested fix:
I should be able to insert the values like 1970-01-01 05:29:01
i.e. any date/time value since midnight of 1970-01-01

Timestamp are stored in UTC .. so this is a consequence of that I believe!

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html:
"TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval."

http://dev.mysql.com/doc/refman/5.0/en/datetime.html:
"For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038."

.. I think first document needs a clarification (at the top) that

"The TIMESTAMP ... supported range is  '1070-01-01 00:00:00' to '2037-12-31 23:59:59' UTC. "

similar to and close to

"The DATETIME ... supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'."

-- and btw this is wrong "or later than 2038".  It is *later than 2037* I believe!  Inserting '2038-05-31 23:59:59' (what is *later than 2037* but not *later than 2038*) fails and inserting '2037-05-31 23:59:59' succeeds.

Peter
(not a mysql person)

Thank you for the report.

As Peter already described this is expected behavior. So I close the report as "Not a Bug".

Peter, thank you for the detailed explanation.