Add microseconds support on datetime/time types

[lcobucci]

This was suggested on doctrine/orm#6510, the user has the need of sorting entries with a more accurate precision. Some comments about this on #1515

[PabloKowalczyk]

Any progress with this issue?

[nick4fake]

It was a really unexpected finding. Are there any workarounds?

[PabloKowalczyk]

@nick4fake Yes, you can write something like this: https://github.com/PabloKowalczyk/Todora/blob/master/src/DateTimeImmutableMicrosecondsType.php
This class extends Doctrine\DBAL\Types\VarDateTimeImmutableType so it will be preserving microseconds when fetching date from DB and convertToDatabaseValue implementation will save microseconds to DB.

Note: This class covers only \DateTimeImmutable instances. If you need working schema tool you should also override getSQLDeclaration method.

Don't forget about type override:
Symfony:

doctrine:
    dbal:
        types:
            datetime_immutable: \Todora\DateTimeImmutableMicrosecondsType

Also you can read this: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/known-vendor-issues.html#datetime-datetimetz-and-time-types

[ossinkine]

Adding .u to AbstractPlatform.php#L3356 does not solve the issue?

[Majkl578]

Before this can be considered, portability needs to be assessed, since the underlying data types must support saving fractional seconds.

• PostgreSQL: supports 0-6 fractional seconds: https://www.postgresql.org/docs/10/static/datatype-datetime.html
• MySQL: supports 0-6 fractional seconds since 5.7: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
• SQLite: has no built-in date/time type, but date/time functions support 0 or 3 fractional seconds: https://www.sqlite.org/lang_datefunc.html
• Oracle: suports 0-9 fractional seconds: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ
• DB2: supports 0-12 fractional seconds: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_timestampvalues.html
• MariaDB: ?
• SQL Server: ?
• SQL Anywhere: ?
• SQL Azure: ?

@morozov What do you think about this for 3.0?

[morozov]

@Majkl578 as for the portability assessment, I think we already have a decent majority (SQL Server supports fractions too). It doesn't look like a breaking change, so it can go to master.

[VasekPurchart]

MariaDB: ?

MariaDB has also 0-6 fractional seconds from 5.3 https://mariadb.com/kb/en/library/microseconds-in-mariadb/

[kiler129]

The status as of now:

Based on \Doctrine\DBAL\Platforms\AbstractPlatform::getDateTimeFormatString:

• SQLServer & SQLServer2008 forces .000 as microseconds
• SQLAnywhere supports .u
• Default sets no microseconds

So changing this is trivial, we just need to ensure it will not explode and this needs to be considered on per platform basis as Majkl said.