using custom type in Criteria expression

[tklaas]

I created a custom type "unixtime" (see end of post) that let me use my old database with timestamps saved as simple integer instead of datetime fields.

in my doctine entity i have:

   /**
     * @var \DateTime
     * @ORM\Column(type="unixtime")
     */
    private $start;

    /**
     * @var \DateTime
     * @ORM\Column(type="unixtime")
     */
    private $end

I created a criteria in my PromotionRepository class that checks these fields to define my condition in only one place

   public static function createActiveCriteria($alias = null)
    {
        $now = new \DateTime();
        $alias = ($alias ? $alias.'.' : '');
        return Criteria::create()
            ->andWhere(Criteria::expr()->lt($alias.'start', $now))
            ->andWhere(Criteria::expr()->gt($alias.'end', $now))
            ;
    }

This method is used in 2 cases to check if a promotion is active on a product

1. in my product entity to filter the list of all promotions for this product:

    /**
     * @var Promotion[]|ArrayCollection
     * @ORM\OneToMany(targetEntity="App\Entity\Promotion", mappedBy="conditionProduct")
     */
    private $promotions;

    /**
     * @return Promotion|false
     */
    public function getActivePromotion()
    {
        return $this->promotions->matching(
            PromotionRepository::createActiveCriteria()
        )->first();
    }

2. in my PromotionRepository

    /**
     * @return Promotion[]
     */
    public function findAllActivePromotions()
    {
        $qb = $this->createQueryBuilder('promotion')
            ->addCriteria(PromotionRepository::createActiveCriteria())
        ;
        return $qb->getQuery()->execute();
    }

In Symfony Profiler, I can see, that SQL queries for the first case (using on ArrayCollection) contain the correct timestamp as integer, in the second case (using in query) it is converted to a DateTime String "2018-02-28 10:49:27"

A similar issues was already mentioned in #5695 and the workaround was to set the Type explicitely on "setParameter" but I do not see a way to set the type on my Criteria

My custom type
registered in doctrine.yaml

doctrine:
    dbal:
        types:
            unixtime: App\Doctrine\Type\UnixtimeType

class UnixtimeType extends Type
{

    const UNIXTIME = 'unixtime';

    /**
     * Gets the SQL declaration snippet for a field of this type.
     *
     * @param array                                     $fieldDeclaration The field declaration.
     * @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform         The currently used database platform.
     *
     * @return string
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return $platform->getIntegerTypeDeclarationSQL($fieldDeclaration);
    }

    /**
     * @param \DateTime            $value
     * @param AbstractPlatform $platform
     *
     * @return int
     */
    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        if($value instanceof \DateTime) {
            return $value->getTimestamp();
        }
        return 0;
    }

    /**
     * @param mixed            $value
     * @param AbstractPlatform $platform
     *
     * @return \DateTime
     */
    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return date_create()->setTimestamp($value);
    }

    public function getBindingType()
    {
        return \PDO::PARAM_INT;
    }

    /**
     * Gets the name of this type.
     *
     * @return string
     */
    public function getName()
    {
        return self::UNIXTIME;
    }
}

[ostrolucky]

This behaviour seems expected to me. You specified custom DBAL type on a specific field so doctrine knows how to convert value when writing/reading from the field. But it doesn't know what to do with value you are comparing that field with. You'll need to explicitly cast datetime object when using this parameter but wanting to treat it as unix time.

[ostrolucky]

Closing as explained