MySQL Bugs: #21351: Inserting wrong tiime

Bug #21351	Inserting wrong tiime
Submitted:	30 Jul 2006 15:29	Modified:	30 Aug 2006 13:26
Reporter:	MARIO SANCHEZ	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.58	OS:
Assigned to:		CPU Architecture:	Any

Description:
Old dates cause problems ...
Consider

CREATE TABLE `timeplay` ( `autoinc` tinyint(4) NOT NULL auto_increment, `atime` timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`autoinc`) ) TYPE=MyISAM;

Now, adding two records,
INSERT INTO timeplay (`atime`) VALUES ('20060730110729');
INSERT INTO timeplay (`atime`) VALUES ('19560305081223');

select * from timeplay; ..

autoinc 	atime
1 	20060730110729
2 	20560305081223

Note that the 1956 year has been inserted as 2056. BUT YET ..
DATE_FORMAT(atime, '%W %M %D, %Y at %r') from timeplay;
Sunday July 30th, 2006 at 11:07:29 AM
Monday March 5th, 1956 at 08:12:23 AM

gives the CORRECT YEAR!

How to repeat:
see the code above

update - the insertion date/time is without quotes.

Thank you for a problem report. Please, specify the exact version of MySQL server used.

-- Server version	3.23.58

should have included same in orig posting-apologies.

thanks

Sorry, but I was not able to repeat the behaviour described with 3.23.59-BK on Linux:

openxs@suse:~/dbs/3.23> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.59-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `timeplay` ( `autoinc` tinyint(4) NOT NULL auto_increment,
`atime`
    -> timestamp NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`autoinc`
) )
    -> TYPE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO timeplay (`atime`) VALUES ('20060730110729');
IQuery OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timeplay (`atime`) VALUES ('19560305081223');
Query OK, 1 row affected (0.00 sec)

mysql> select * from timeplay;
+---------+----------------+
| autoinc | atime          |
+---------+----------------+
|       1 | 20060730110729 |
|       2 | 00000000000000 |
+---------+----------------+
2 rows in set (0.01 sec)

mysql> select DATE_FORMAT(atime, '%W %M %D, %Y at %r') from timeplay;
+------------------------------------------+
| DATE_FORMAT(atime, '%W %M %D, %Y at %r') |
+------------------------------------------+
| Sunday July 30th, 2006 at 11:07:29 AM    |
| NULL                                     |
+------------------------------------------+
2 rows in set (0.00 sec)

What we have above is expected behaviour. Read the manual, http://dev.mysql.com/doc/refman/4.1/en/datetime.html, for the details:

"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 valid as a TIMESTAMP value and is converted to 0."