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