Symfony 6, Doctrine, DateTime and microseconds

Not tested in production

This is not tested on a stage or production environment yet – and I’m sure I forget to remove this warning, once it is properly tested. Feel free to write a comment, so we can update this post 🙂

Doctrine isn’t yet able to handle microseconds, if I understand correctly, especially because they are not sure how to handle the on the difference database management systems.

But thankfully it is relatively simple to implement our own type (or overwrite the datetime type).

Implement your own type

Thanks to flauschi, I was able to adapt his code for our purpose (especially without using Carbon)

<?php

declare(strict_types=1);

namespace Winkelwagen\Dbal;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Types\ConversionException;
use Doctrine\DBAL\Types\Type;

class DateTimeWithMicroseconds extends Type
{
    private const TYPENAME = 'datetime';

    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        if (isset($column['version']) && $column['version'] === true) {
            return 'TIMESTAMP';
        }

        if ($platform instanceof PostgreSqlPlatform) {
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        }

        return 'DATETIME(6)';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform): mixed
    {
        if ($value === null || $value instanceof \DateTimeInterface) {
            return $value;
        }

        if (str_contains($value, '.')) {
            return \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', $value);
        }

        return \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $value);
    }

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

        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(): string
    {
        return self::TYPENAME;
    }

    public function requiresSQLCommentHint(AbstractPlatform $platform): bool
    {
        return true;
    }
}

Configure doctrine to use it

With this class and the following configuration, you are set:

# config/packages/doctrine.yaml

doctrine:
    dbal:
        types:
            datetime: \Paddox\Dbal\DateTimeWithMicroseconds

To change the name of the type, you need to change the YAML key and the PHP constant.

gist with everything

<?php
declare(strict_types=1);
namespace Winkelwagen\Dbal;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Types\ConversionException;
use Doctrine\DBAL\Types\Type;
class DateTimeWithMicroseconds extends Type
{
private const TYPENAME = 'datetime';
public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
{
if (isset($column['version']) && $column['version'] === true) {
return 'TIMESTAMP';
}
if ($platform instanceof PostgreSqlPlatform) {
return 'TIMESTAMP(6) WITHOUT TIME ZONE';
}
return 'DATETIME(6)';
}
public function convertToPHPValue($value, AbstractPlatform $platform): mixed
{
if ($value === null || $value instanceof \DateTimeInterface) {
return $value;
}
if (str_contains($value, '.')) {
return \DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', $value);
}
return \DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $value);
}
public function convertToDatabaseValue($value, AbstractPlatform $platform): mixed
{
if (null === $value) {
return null;
}
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(): string
{
return self::TYPENAME;
}
public function requiresSQLCommentHint(AbstractPlatform $platform): bool
{
return true;
}
}
<?php
declare(strict_types=1);
namespace Winkelwagen\Dbal;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Types\ConversionException;
use JetBrains\PhpStorm\ArrayShape;
use PHPUnit\Framework\TestCase;
class DateTimeWithMicrosecondsTest extends TestCase
{
private DateTimeWithMicroseconds $type;
protected function setUp(): void
{
parent::setUp();
$this->type = new DateTimeWithMicroseconds();
}
public function testSqlDeclarationIsTimestampForVersionTrue(): void
{
$column = ['version' => true];
$platform = $this->createMock(AbstractPlatform::class);
$this->assertSame('TIMESTAMP', $this->type->getSQLDeclaration($column, $platform));
}
public function testSqlDeclarationForPostgre(): void
{
$column = [];
$platform = $this->createMock(PostgreSqlPlatform::class);
$this->assertSame('TIMESTAMP(6) WITHOUT TIME ZONE', $this->type->getSQLDeclaration($column, $platform));
}
public function testSqlDeclarationDefaultIsTimestamp6(): void
{
$column = [];
$platform = $this->createMock(AbstractPlatform::class);
$this->assertSame('DATETIME(6)', $this->type->getSQLDeclaration($column, $platform));
}
/**
* @dataProvider provideTimestamps
*/
public function testConvertToPhpValue($value, $expected): void
{
$platform = $this->createMock(AbstractPlatform::class);
$this->assertEquals($expected, $this->type->convertToPHPValue($value, $platform));
}
#[ArrayShape([
'null' => "null[]",
'with zero microseconds' => "array",
'without microseconds' => "array",
'with 200 microseconds' => "array"
])] public function provideTimestamps(): array
{
return [
'null' => [null, null],
'with zero microseconds' => ['2001-01-03 12:46:18.000', new \DateTimeImmutable('2001-01-03 12:46:18.000')],
'without microseconds' => ['2001-01-03 12:46:18', new \DateTimeImmutable('2001-01-03 12:46:18.000')],
'with 200 microseconds' => ['2001-01-03 12:46:18.200', new \DateTimeImmutable('2001-01-03 12:46:18.200')],
];
}
/**
* @dataProvider provideDateTimeObject
*/
public function testConvertToDatabaseValue($value, $expected): void
{
$platform = $this->createMock(AbstractPlatform::class);
$this->assertSame($expected, $this->type->convertToDatabaseValue($value, $platform));
}
#[ArrayShape([
'null' => "null[]",
'with zero microseconds' => "array",
'without microseconds' => "array",
'with 200 microseconds' => "array"
])] public function provideDateTimeObject(): array
{
return [
'null' => [null, null],
'with zero microseconds' => [
new \DateTimeImmutable('2001-01-03 12:46:18.000'),
'2001-01-03 12:46:18.000000'
],
'without microseconds' => [new \DateTimeImmutable('2001-01-03 12:46:18.000'), '2001-01-03 12:46:18.000000'],
'with 200 microseconds' => [
new \DateTimeImmutable('2001-01-03 12:46:18.200'),
'2001-01-03 12:46:18.200000'
],
];
}
public function testThrowsExceptionIfNotDateTimeOrNullOnConvertToDatabase(): void
{
$this->expectException(ConversionException::class);
$platform = $this->createMock(AbstractPlatform::class);
$this->type->convertToDatabaseValue('not an object', $platform);
}
public function testGetName(): void
{
$this->assertSame('datetime', $this->type->getName());
}
public function testRequiresSqlCommentHint(): void
{
$platform = $this->createMock(AbstractPlatform::class);
$this->assertTrue($this->type->requiresSQLCommentHint($platform));
}
}
doctrine:
dbal:
types:
datetime: \Paddox\Dbal\DateTimeWithMicroseconds
view raw doctrine.yaml hosted with ❤ by GitHub

Kommentar verfassen