Database Column Type: DateTime with Microseconds with DBAL

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 2022–04–18 03:44:12.649267. This is not natively supported by the Doctrine DBAL Package. At the moment we are running PHP 8.1.0, Symfony 6.0.7, doctrine/doctrine-bundle 2.6.1 and MySQL 5.7.26.

Here’s how we solved it:

namespace App\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
    ) string {
        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(
        mixed $value,
        AbstractPlatform $platform
    ) mixed {
        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(
        mixed $value,
        AbstractPlatform $platform
    ) mixed {
        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 type, we need to somehow override the built-in DateTimeType in the Doctrine Type system:

use Doctrine\DBAL\Types\Type;
use Easycharter\System\Infrastructure\DbalTypes\DateTimeType;
...
Type::overrideType(Type::DATETIME, DateTimeType::class);