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;
}
}
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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 ));
}
}
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Great work. Has it been tested in PROD yet and can you explain line 18-20 where you check for ‘version’ please?
No, it wasn’t deployed to live. We found another solution and removed it again 😅
And no, I have no clue, what this version is. But I assume if the version is not set it is THAT old, that we can’t use TIMESTAMP(6)