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:
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:ALL OS:Any
Assigned to: CPU Architecture:Any
Tags: timestamp, timezone

[19 Apr 2008 9:04] Shantanu Oak
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

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
[19 Apr 2008 12:04] Peter Laursen
Timestamp are stored in UTC .. so this is a consequence of that I believe!

"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."

"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.

(not a mysql person)
[25 Apr 2008 9:52] Sveta Smirnova
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.