Skip to content

Instantly share code, notes, and snippets.

@keksa
Last active February 2, 2021 09:51
Show Gist options
  • Save keksa/0fcb74736692cdc05e9ad03636cd2667 to your computer and use it in GitHub Desktop.
Save keksa/0fcb74736692cdc05e9ad03636cd2667 to your computer and use it in GitHub Desktop.
PostgreSQL interval type for Doctrine (PHP)
<?php
declare(strict_types=1);
namespace AppBundle\Database;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;
/**
* @see https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/cookbook/custom-mapping-types.html
*/
class IntervalType extends Type
{
public function getName(): string
{
return 'interval';
}
public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
{
return 'interval';
}
public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
if (! $value instanceof \DateInterval) {
return null;
}
$reference = new \DateTimeImmutable();
return sprintf('%d seconds', $reference->add($value)->getTimestamp() - $reference->getTimestamp());
}
public function convertToPHPValue($value, AbstractPlatform $platform)
{
if ($value === null) {
return null;
}
return new \DateInterval(sprintf('PT%dS', (int) $value));
}
public function canRequireSQLConversion(): bool
{
return true;
}
/**
* Convert the postgres interval to total number of seconds in it for convertToPHPValue purposes.
*
* It's possible to select the interval in iso_8601 format, which is usable by \DateInterval,
* but that requires calling `SET intervalstyle = 'iso_8601';` query and that can't be easily done with Doctrine.
*/
public function convertToPHPValueSQL($sqlExpr, $platform): string
{
return sprintf('EXTRACT(EPOCH FROM %s)', $sqlExpr);
}
public function requiresSQLCommentHint(AbstractPlatform $platform): bool
{
return true;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment