Database Column Type: DateTime with Microseconds

Last week we needed to add a datetime column in a database table. The issue was that we needed this datetime with microseconds, so something like 2019-10-31 07:23:38.774932. This is not supported by the Doctrine DBAL Package. At the moment we are running PHP 7.3.9, Symfony 4.3.5, Doctrine/DBAL 2.9.2 and MySQL 5.7.26. Here’s how we solved it:

namespace App\System\Infrastructure\DbalTypes; 

use DateTime;
use DateTimeInterface;
use Doctrine\DBAL\Platforms\AbstractPlatform; 
use Doctrine\DBAL\Platforms\PostgreSqlPlatform; 
use Doctrine\DBAL\Types\ConversionException; 
use Doctrine\DBAL\Types\DateTimeType as DbalDateTimeType; 
use Doctrine\DBAL\Types\Type; 

final class DateTimeType extends DbalDateTimeType 
{ 
    public function getName() { 
        return Type::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 convertToDatabaseValue($value, AbstractPlatform $platform) 
    { 
        if ($value === null) { 
            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 convertToPHPValue($value, AbstractPlatform $platform)
    {
        if ($value === null || $value instanceof DateTimeInterface) {
            return $value;
        }

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

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

        if (!$val) {
            throw ConversionException::conversionFailedFormat($value, $this->getName(), $platform->getDateTimeFormatString());
        }

        return $val;
    }
}

Now that we have the new configurations, we need to somehow overwrite those built-in into Doctrine. To do that, we added the following to the bootstrap.php file in the config folder:

use Doctrine\DBAL\Types\Type;
use App\System\Infrastructure\DbalTypes\DateTimeType;

...

Type::overrideType(Type::DATETIME, DateTimeType::class);