MySQL Datetime precision… I think not!

We use Hibernate at work, and I’ve been working on getting all our JUnit tests to work on MySQL… Yes hibernate takes care of most of that, and it does, BUT some of our unit tests were failing when I pointed it at MySQL rather then PostgreSQL.

After some debugging I noticed that when we pulled one of our objects out of the database via hibernate, the Date object inside wasn’t the same as the Date inserted:
originalDate.getTime(); // = 1261613807262
retrievedDate.getTime(); // = 1261613807000

The two epoch dates are almost the same, except the last three digits are zero’d out. The keen observers might have already figured out these are microseconds.
It seems MySQL isn’t storing or retrieving the microseconds from the datetime datatype.  Even though MySQL does have the MICROSECOND() function. A quick search on Google supports my findings there is a “Feature Request” opened on the MySQL bug tracker, annoyingly though it was opened on 15 Feb 2005, yet nothing is yet implemented.

For those who want to get around this issue, and want to Assert the dates, and want them to actually work, you simply need to zero out the microseconds component of the date, here is the simple function I used:
private Date removeMicroseconds(Date date) {
return new Date(date.getTime() - (date.getTime() % 1000));
}