Add microseconds support on datetime/time types

[lcobucci]

This was suggested on https://github.com/doctrine/doctrine2/issues/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.

[TomHAnderson]

This PR was started because I created a custom type for date time with microseconds:

https://github.com/doctrine/dbal/pull/3291

[rquadling]

~> * MySQL: supports 0-6 fractional seconds since 5.7:~ ~https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html~

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

[flaushi]

Is it possible to just overwrite or re-register the datetime type? (.u should work on postgres and mysql)

In a symfony-setting like so:

doctrine:
    dbal:
        types:
            datetime: App\DBAL\Types\CarbonMicrosecondsType

class CarbonMicrosecondsType extends Type
{
    const TYPENAME = 'datetime';

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {
            return 'TIMESTAMP';
        }
        if($platform instanceof PostgreSqlPlatform)
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        else
            return 'DATETIME(6)';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        if($value === null || $value instanceof CarbonInterface)
            return $value;

        if ($value instanceof DateTimeInterface) {
            return Carbon::instance($value);
        }

        $val = Carbon::createFromFormat('Y-m-d H:i:s.u', $value);

        if ( ! $val) {
            $val = Carbon::instance(date_create($value));
        }

        if ( ! $val) {
            throw ConversionException::conversionFailedFormat(
                $value,
                $this->getName(),
                'Y-m-d H:i:s.u'
            );
        }

        return $val;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (null === $value) {
            return $value;
        }

        if ($value instanceof DateTimeInterface) {
            //var_dump('inner '.$value->format('Y-m-d H:i:s.u'));
            return $value->format('Y-m-d H:i:s.u');
        }

        throw ConversionException::conversionFailedInvalidType(
            $value,
            $this->getName(),
            ['null', 'DateTime']
        );
    }

    public function getName()
    {
        return self::TYPENAME;
    }
    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }
}

[PabloKowalczyk]

@flaushi please remember that not everyone have Carbon installed, more portable version will be with DateTime(Immutable).

[flaushi]

@PabloKowalczyk Yes, absolutely, but one can replace Carbon with DateTime eaasily. For the sake of completeness, I solved my problem posted above. The reason was that postgres returns in format Y-m-d H:i:s if microseconds are set to zero, and in format Y-m-d H:i:s.u if not. So I finally use this type, which works fine:

class CarbonMicrosecondsType extends Type
{
    const TYPENAME = 'datetime';

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {
            return 'TIMESTAMP';
        }
        if($platform instanceof PostgreSqlPlatform)
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        else
            return 'DATETIME(6)';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        if($value === null || $value instanceof CarbonInterface)
            return $value;

        if ($value instanceof DateTimeInterface) {
            return Carbon::instance($value);
        }
        $val = DateTime::createFromFormat('Y-m-d H:i:s.u', $value);

        if ( ! $val) {
            $val = Carbon::instance(date_create($value));
        } else {
            $val = Carbon::instance($val);
        }

        if ( ! $val) {
            throw ConversionException::conversionFailedFormat(
                $value,
                $this->getName(),
                'Y-m-d H:i:s.u'
            );
        }

        return $val;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if (null === $value) {
            return $value;
        }

        if ($value instanceof DateTimeInterface) {
            return $value->format('Y-m-d H:i:s.u');
        }

        throw ConversionException::conversionFailedInvalidType(
            $value,
            $this->getName(),
            ['null', 'DateTime']
        );
    }

    public function getName()
    {
        return self::TYPENAME;
    }
    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }
}

[siraic]

I overwrote the DateTimeImmutable type, made the length dependant on the 'precision' fieldDeclaration, and simply added '.u' to the datetime formats. It caused some unexpected behaviour because by default mysql rounds times instead of truncates.

We're used to truncating times in PHP (with the format method you only print the parts you need), but mysql rounds the values that fall outside the column definition. This behaviour can be changed in mysql 8: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_time_truncate_fractional.

When using mysql 5.6, 5.7 or 8 without the TIME_TRUNCATE_FRACTIONAL mode, be aware that sending 2018-12-31 23:59:59.50000 to a DATETIME(0) field will be stored as 2019-01-01 00:00:00

[flaushi]

Yes, mysql and postgres have strange behaviours, I agree. A really good advice is to use date_create($value), because it seems to be quite flexible in parsing the timestamps with and without microseconds.

[flaushi]

I am coming back to this issue, because now we have a use case e.g. in https://github.com/symfony/symfony/issues/33785.

I tried to change the used type in symfony messenger, but it relies on doctrine's builtin datetime type. Would it at least be possible to include a new microseconds type in doctrine, s.t. the referenced issue could be solved by using this "official" type?

I can well understand that the symfony team will not be willing to introduce a custom DBAL type on their side, but just selecting a new microseconds-based one will be possible I guess.

[kylekatarnls]

Hi, please let me reanimate this issue.

I found that microseconds-precision (6) was the default for SQLServer2012Platform: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServer2012Platform.php#L1156

But all other platforms had (0). I would say the dates should be stored with the maximal precision available for proper sorting and comparisons.

And it seems relevant than the "precision" field declaration option should work the same way for floating numbers and dates. So I would expect an annotation like: @ORM\Column(type="datetime", precision=3) would set the datetime precision to 3 (milliseconds) and the default annotation @ORM\Column(type="datetime") would set the precision to 6 by default.

If the precision would exceed the maximum supported by the platform, this maximum should be used instead.

Is there anything preventing us to implement it that way? If not, I would be happy to propose a pull-request for that.

[flaushi]

@kylekatarnls, looks good. But is there any platform that does not support microseconds (precision = 6)?

[kylekatarnls]

According to @Majkl578 There is at least SQLite that only support 0 or 3: https://github.com/doctrine/dbal/issues/2873#issuecomment-413859159

And some other platforms/versions to be checked.

But there is Travis unit tests in this repo to check every supported platform, so we could just give a try and add unit tests for the precision and see if a platform fail on Travis.

[flaushi]

Yes, right, SQLite doesn't make thinks easier all the time...

So, great idea!

[kylekatarnls]

So here is the summary:

PostgreSQL: 0-6: https://www.postgresql.org/docs/10/static/datatype-datetime.html MySQL: 0-6 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: 0-9: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ DB2: 0-12: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_timestampvalues.html MariaDB: 0-6 since 5.3: https://mariadb.com/kb/en/library/microseconds-in-mariadb/ SQL Server: 0-7: https://docs.microsoft.com/fr-fr/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15 SQL Anywhere: 0-6: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/datetime-date-wsqltype.html SQL Azure: see SQL Server

I would add that custom types like the one proposed by @flaushi here https://github.com/doctrine/dbal/issues/2873#issuecomment-535452749 has no way to distinguish precision set to 0 from precision not set. Annotation (int) cast replace null with 0 so both appears as 0 in $fieldDeclaration it's too bad because if you set a default to 6 in a custom type you would not be able to use precision=0 in ORM\Column() annotation, it would work with any value but 0.

As a first step precision should be passed as null (for date types) when not set. As it's purely ignored for the moment by the internal types it would make no difference for existing types but it would allow custom types choose an other default value and allowing customization.

[akalineskou]

I added microseconds to datetime (for postgres v10+), by extending the postgres platform to return the timeformat with the microseconds, added the platform service in doctrine config, extended the datetime type to return the correct sql declaration for postgres and overriding the type in doctrine config.

The migrations I had to manually create (since the diff doesn't see the change), but afterwards the datetime fields are created with the correct sql declaration.

doctrine.yaml

doctrine:
    dbal:
        platform_service: PostgreSQLMicrosecondsPlatform

        types:
            datetime: DateTimeMicrosecondsType

PostgreSQLMicrosecondsPlatform.php

<?php
use Doctrine\DBAL\Platforms\PostgreSQL100Platform;

class PostgreSQLMicrosecondsPlatform extends PostgreSQL100Platform
{
    /**
     * @inheritDoc
     */
    public function getDateTimeFormatString()
    {
        return sprintf('%s.u', parent::getDateTimeFormatString());
    }
}

DateTimeMicrosecondsType.php

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQL100Platform;
use Doctrine\DBAL\Types\DateTimeType;

class DateTimeMicrosecondsType extends DateTimeType
{
    /**
     * @inheritDoc
     */
    public function getSQLDeclaration(array $column, AbstractPlatform $platform)
    {
        if ($platform instanceof PostgreSQL100Platform) {
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        }

        return parent::getSQLDeclaration($column, $platform);
    }
}

[rquadling]

Just in case anyone is using a VERY old version of PHP and Doctrine, and is wondering why it may be that fractional seconds are not working as expected, there was a bug:76386 in mysqlnd that was fixed in PHP 7.3.0.

[jzecca]

@akalineskou

I added microseconds to datetime (for postgres v10+), by extending the postgres platform to return the timeformat with the microseconds, added the platform service in doctrine config, extended the datetime type to return the correct sql declaration for postgres and overriding the type in doctrine config.

Is there a reason why you extend the DateTime type instead of just setting its declaration in the Platform?

This seems to work for me (but I'm no expert when it comes to Doctrine internals):

# config/packages/doctrine.yaml
doctrine:
    dbal:
        platform_service: App\Doctrine\Platform\PostgreSQLPlatform

namespace App\Doctrine\Platform;

use Doctrine\DBAL\Platforms\PostgreSQL100Platform;

class PostgreSQLPlatform extends PostgreSQL100Platform
{
    public function getDateTimeFormatString(): string
    {
        return str_replace('s', 's.u', parent::getDateTimeFormatString());
    }

    public function getDateTimeTzFormatString(): string
    {
        return str_replace('s', 's.u', parent::getDateTimeTzFormatString());
    }

    public function getTimeFormatString(): string
    {
        return str_replace('s', 's.u', parent::getTimeFormatString());
    }

    public function getDateTimeTypeDeclarationSQL(array $column): string
    {
        return str_replace('(0)', '(6)', parent::getDateTimeTypeDeclarationSQL($column));
    }

    public function getDateTimeTzTypeDeclarationSQL(array $column): string
    {
        return str_replace('(0)', '(6)', parent::getDateTimeTzTypeDeclarationSQL($column));
    }

    public function getTimeTypeDeclarationSQL(array $column): string
    {
        return str_replace('(0)', '(6)', parent::getTimeTypeDeclarationSQL($column));
    }
}

[akalineskou]

@jzecca Can't remember exactly why, but I think I wanted to just change the datetime without altering the others as a proof of concept...

[tyteen4a03]

+1 - we've had to put in a workaround for 10 years now so would be great to finally see this fixed.

[isodude]

In MariaDB there's a bug (I think?) that converts DATETIME(0) to DATETIME(6) when running SELECT GREATEST(field1, COALESCE(field2, 0)) ... It works properly if either GREATEST or COALESCE is not nested.

[mtournay]

Hi

according to the response in this thread, it's not a bug

https://jira.mariadb.org/browse/MDEV-4560?jql=text%20~%20%22greatest%20datetime%22

[isodude]

Hi

according to the response in this thread, it's not a bug

https://jira.mariadb.org/browse/MDEV-4560?jql=text%20~%20%22greatest%20datetime%22

I think it's not related

(MariaDB 10.5.16)

MariaDB [(none)]> SELECT GREATEST('2015-01-01 00:00:00', NOW());
+----------------------------------------+
| GREATEST('2015-01-01 00:00:00', NOW()) |
+----------------------------------------+
| 2022-08-23 11:55:21                    |
+----------------------------------------+
1 row in set (0.001 sec)

And here's the 'bug', it seems that converting 0 to datetime(0) makes the problem go away.

*************************** 1. row ***************************
working: 2022-08-23 12:34:33
  wrong: 2022-08-23 12:34:33.000000

CREATE TEMPORARY TABLE MyInlineTable (id LONG, created DATETIME(0), updated DATETIME(0) );

INSERT INTO MyInlineTable VALUES
(1, NOW(),NOW());

SELECT
  GREATEST(created, COALESCE(updated, CONVERT('0', DATETIME(0)))) AS working,
  GREATEST(created, COALESCE(updated, 0)) AS wrong
FROM MyInlineTable \G

[mtournay]

Hi

indeed you're right : forcing type resolves issue, and it's what i did explain here

https://github.com/akeneo/pim-community-dev/issues/17711

This is a huge difference between mysql/maria, and honestly I don't know what to think. Obviously it's not to doctrine to manage that, but to sql script to be compatible with both engines