NOTE: This doesn’t generate a timestamp accurate to milliseconds. It just stores it in two fields. AFAIK there is no way to generate such a thing. It is accurately described here. Sorry for the confusion =)

Bug #8523 details MySQL’s deficiency in its ability to store and retrieve datetime values with millisecond or microsecond precision. When performance testing and perhaps storing trace logs or audit information in a MySQL table for later analysis, this shortfall is quite frustrating.

My hacky way to deal with this is to store the millisecond component as its own integer. Say for example you are interested in the difference between a Put datetime and Get datetime for MQ, your table might look like this:

CREATE TABLE `msecs` (
  `put_datetime` datetime default NULL,
  `put_msecs` int(11) default NULL,
  `get_datetime` datetime default NULL,
  `get_msecs` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So in this table I am storing the datetime component in its native MySQL format and the millisecond component as an integer. Here are some examples:
datetime msecs mysql

Now to retrieve and compare the values, I get the difference using TIME_TO_SEC for the datetime component, multiply it by 1000 to convert to milliseconds then add to the difference of the milliseconds component using basic maths:

SELECT ((TIME_TO_SEC(get_datetime) - TIME_TO_SEC(put_datetime))*1000)
+ (get_msecs-put_msecs)
AS diff_msecs
FROM msecs

That gives you difference in milliseconds for the demo table along these lines:
2300
1700
300
600700
700

Hopefully that makes sense. Pipe up if you have an easier solution!

About these ads