MySQL Bugs: #27838: DateTime and Timestamp do not work with Milliseconds

Bug #27838	DateTime and Timestamp do not work with Milliseconds
Submitted:	15 Apr 2007 23:01	Modified:	16 Apr 2007 4:33
Reporter:	Andreas Freyer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

DateTime and Timestamp do not store Milliseconds (more precise: Fractions of Seconds).

Try this:
=========
CREATE TABLE `testtable` (  `id` bigint(20) NOT NULL auto_increment,  `dateAndTime` datetime NOT NULL,  `timeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  `text` varchar(255) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
INSERT INTO `testtable` ( `dateAndTime` ) VALUES ('2007-11-12 20:21:22.331');
INSERT INTO `testtable` ( `timeStamp` ) VALUES ('2007-11-12 20:21:22.332');
SELECT DATE_FORMAT(dateAndTime, '%Y-%m-%d %T.%f') as dtWithMilliSeconds, DATE_FORMAT(timeStamp, '%Y-%m-%d %T.%f') as tsWithMilliSeconds from `testtable`;

And the Result will be:
=======================
dtWithMilliSeconds   	 tsWithMilliSeconds
2007-11-12 20:21:22.000000 	2007-04-15 18:53:46.000000
0000-00-00 00:00:00.000000 	2007-11-12 20:21:22.000000

A Workaround would be to store the microseconds in a separate field but migrating from existing applications running also with other databases without having the source-code is impossible.

Please fix it urgently....Thank you very much!

Andreas  

How to repeat:
Try this:
=========
CREATE TABLE `testtable` (  `id` bigint(20) NOT NULL auto_increment,  `dateAndTime` datetime NOT NULL,  `timeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  `text` varchar(255) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
INSERT INTO `testtable` ( `dateAndTime` ) VALUES ('2007-11-12 20:21:22.331');
INSERT INTO `testtable` ( `timeStamp` ) VALUES ('2007-11-12 20:21:22.332');
SELECT DATE_FORMAT(dateAndTime, '%Y-%m-%d %T.%f') as dtWithMilliSeconds, DATE_FORMAT(timeStamp, '%Y-%m-%d %T.%f') as tsWithMilliSeconds from `testtable`;

And the Result will be:
=======================
dtWithMilliSeconds   	 tsWithMilliSeconds
2007-11-12 20:21:22.000000 	2007-04-15 18:53:46.000000
0000-00-00 00:00:00.000000 	2007-11-12 20:21:22.000000

Please do not submit the same bug more than once. An existing bug report, bug #8523, already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

I think that if bug #8523 has not been answered to in 2 years, it is only natural that duplicate bug reports should appear.

I understand that this problem might not be easy to fix, but it is in my opinion a serious problem for your users and giving better feedback would be nice.

Thanks, 

jG