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);