Why doesn't MySQL support millisecond / microsecond precision?

php mysql doctrine
Byron Whitlock picture Byron Whitlock · Apr 3, 2010 · Viewed 53k times · Source

So I just found the most frustrating bug ever in MySQL.

Apparently the TIMESTAMP field, and supporting functions do not support any greater precision than seconds!?

So I am using PHP and Doctrine, and I really need those microseconds (I am using the actAs: [Timestampable] property).

I found a that I can use a BIGINT field to store the values. But will doctrine add the milliseconds? I think it just assigns NOW() to the field. I am also worried the date manipulation functions (in SQL) sprinkled through the code will break.

I also saw something about compiling a UDF extension. This is not an acceptable because I or a future maintainer will upgrade and poof, change gone.

Has anyone found a suitable workaround?

Answer

Xavier Portebois picture Xavier Portebois · Jan 9, 2012

For information for the next readers, this bug has finally be corrected in version 5.6.4:

"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."

Related questions

Doctrine - How to print out the real sql, not just the prepared statement?

We're using Doctrine, a PHP ORM. I am creating a query like this: $q = Doctrine_Query::create()->select('id')->from('MyTable'); and then in the function I'm adding in various where clauses and things as appropriate, …

Read More
Symfony 2: INNER JOIN on non related table with doctrine query builder

I'm trying to build a query with the doctrine query builder which joins a non related table like this: $query = $this->createQueryBuilder('gpr') ->select('gpr, p') ->innerJoin('TPost', 'p') ->where('gpr.contentId = p.…

Read More
How to enable ENUMs in Symfony 2 / Doctrine

When running doctrine:mapping:import i get an error: Unknown database type enum requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it. It seems I need to set use_native_enum to true some how. However, all documentation and blog …

Read More