Bug #9697 Error entering date/time stamp up to 01/01/1970 and earlier

• Submitted: 6 Apr 2005 23:13
• Modified: 6 Apr 2005 23:37
• Reporter: boen liem
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.3 beta
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[6 Apr 2005 23:13] boen liem

Description:

I'm using the command line tool that is delivered together with the server.
Any date after 01/01/1970 is ok and accepted correctedly.

How to repeat:
This is a sample database used by equinox:

mysql> describe app_user;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| id         | bigint(20)   | NO   | PRI |                   |       |
| first_name | varchar(255) | NO   |     |                   |       |
| last_name  | varchar(255) | NO   |     |                   |       |
| birthday   | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
+------------+--------------+------+-----+-------------------+-------+

insert into app_user values(1,'Boen','Liem','1970-02-02 00:00:00');
insert into app_user values(3,'Boens','Liem','1970-01-01 00:00:00');

select * from app_user;
+----+-------------+-----------+---------------------+
| id | first_name  | last_name | birthday            |
+----+-------------+-----------+---------------------+
|  1 | Boen        | Liem      | 1970-02-02 00:00:00 |
|  3 | Boens       | Liem      | 0000-00-00 00:00:00 |
+----+-------------+-----------+---------------------+

[6 Apr 2005 23:15] boen liem

changed the titel 'from' to 'up to'

[6 Apr 2005 23:37] MySQL Verification Team

This is the behavior of timestamp columns. From Manual:

http://dev.mysql.com/doc/mysql/en/datetime.html

Remember that although DATETIME, DATE, and TIMESTAMP  values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and is converted to 0 if assigned to such an object.