6

I have an entity with one column of "datetime" type to store a timestamp.

/**
 * @ORM\Column(type="datetime")
 */
protected $timestamp;

I had MySQL 5.5.40 and I discovered it does not store microseconds. So I switched to 5.6.21 and imported all my tables and data.

I tried to declare the type as 

 * @ORM\Column(type="datetime(6)")

but it gave me an error. So I changed it directly in the DB by doing:

ALTER TABLE symfony.hrmgmt MODIFY timestamp DATETIME(6);

In my controller I do this:

  $dt = new \DateTime('now');
  $newHREvent->setTimestamp($dt);

But nonetheless the timestamp is stored without fractions of second.

I can (now) manually enter datetime with fractional values via SQL, but when I do it through my controller it always stores with .000000

I suppose that's because Doctrine does not know that it can store also microseconds.

My PHP version is still 5.4.34.

Thank you!

Sergio Negri
  • 2,023
  • 2
  • 16
  • 38
  • http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision – Charlotte Dunois Dec 26 '14 at 17:05
  • 2
    I had already read that post. Most answers are referred to pre MySQL 5.6, hence not relevant. The answer from @Xavier Portebois is pertinent and I precisely followed the indication to use DATETIME(6) instead of DATETIME. The problem (as per my question) remains if I try to use doctrine instead of pure SQL. Reading Doctrine documentation I found the "known vendor issues" (http://doctrine-dbal.readthedocs.org/en/latest/reference/known-vendor-issues.html) and there is a reference to microseconds problems in PostgreSQL, but it's not mentioned for MySQL. – Sergio Negri Dec 27 '14 at 08:33
  • Always in the "known vendor issues" (in the PostgreSQL section though) there is this comment:"This is why Doctrine always wants to create the time related types without microseconds...If you do not let Doctrine create the date column types and rather use types with microseconds you have replace the “DateTime”, “DateTimeTz” and “Time” types with a more liberal DateTime parser that detects the format automatically". So is this the confirmation that Doctrine still does not support microseconds? How am I supposed to code the "liberal parsers" mentioned? – Sergio Negri Dec 27 '14 at 08:37
  • Please consider that the microseconds issue is mentioned only under the "PostgreSQL" and the "Microsoft SQL Server", not under MySQL (or other DBs) – Sergio Negri Dec 27 '14 at 08:40
  • I also read here http://doctrine-dbal.readthedocs.org/en/latest/reference/types.html that "datetime" is mapped to DATETIME, but the footnote says it's "Chosen if the column definition not contains the version option inside the platformOptions attribute array or is set to false which marks it as a non-locking information column.". How do I do that? – Sergio Negri Dec 27 '14 at 10:21

2 Answers2

2

tl;dr #

Follow this blog, create your own custom data type and use it https://blog.tomhanderson.com/2018/09/datetime-with-microseconds-for-mysql-in.html

For the patient and curios ones

I am coming here after having gone through the exact steps that you mentioned, upgrading mysql, changing column datatype precision manually, and I was at the exact state as you are...

This is not got to do with PHP version, but instead Doctrine has a role to play here.

Just like for decimal values doctrine supports precision and scale type="decimal" Ex: precision=10, scale=2, it should have supported for datetime, however it doesn't at the moment

The issue is currently being tracking here https://github.com/doctrine/dbal/issues/2873

However, there is a blog that explains a workaround until doctrine fixes this at their end https://blog.tomhanderson.com/2018/09/datetime-with-microseconds-for-mysql-in.html

I believe it was written by Tom H Anderson

Anujith Singh
  • 116
  • 1
  • 7
1

To create a datetime with microsecond you have to use DateTime::createFromFormat in your controller

$date = \DateTime::createFromFormat('U.u', (string)microtime(true));
goto
  • 7,908
  • 10
  • 48
  • 58
  • 1
    The problem is not in the creation of the object at php level, it's at the doctrine level – Sergio Negri Jan 17 '17 at 18:36
  • Yeah but you use `new \DateTime('now');` so it can't work as it does not store micro time – goto Jan 18 '17 at 07:50
  • That's not true in PHP 7. – Arthur Apr 19 '17 at 17:37
  • What is not true? It seems to be workin fine on php 7 [like here](http://sandbox.onlinephpfunctions.com/code/0c4f1e2e02f739eee8ad10e163375322480b9f1e) – goto Apr 19 '17 at 20:52
  • 1
    Sorry, my answer was to your comment. I meant that `new \DateTime('now');` stores micro seconds since PHP 7. EDIT: My bad, PHP 7.1. – Arthur Apr 21 '17 at 16:05