How to compare datetime field from Doctrine2 with a date?

datetime, doctrine-orm, symfony

Solution

one idea is to extract from the date: the year, month and day. And then

$qb->select('p')
   ->where('YEAR(p.postDate) = :year')
   ->andWhere('MONTH(p.postDate) = :month')
   ->andWhere('DAY(p.postDate) = :day');

$qb->setParameter('year', $year)
   ->setParameter('month', $month)
   ->setParameter('day', $day);

MONTH DAY, and YEAR you take out the DoctrineExtensions from

e.g.

DoctrineExtensions

This works for me. You only need the files: day.php, month.php and year.php.....

You get the month e.g.:

    $datetime = new \DateTime("now");
    $month = $datetime->format('m');
    echo $month;

Copy day.php, month.php and year.php to your bundle Xy\TestBundle\Dql Register the new functions in app\config.yml with

doctrine:


orm:
    auto_generate_proxy_classes: %kernel.debug%
    entity_managers:
        default:
            auto_mapping: true
            dql:
                datetime_functions:
                    month: Xy\TestBundle\Dql\Month
                    year: Xy\TestBundle\Dql\Year
                    day: Xy\TestBundle\Dql\Day

Problem

I want to get the items which were created today with by a QueryBuilder from Doctrine2. I want to compare the createdAt(Datetime) field with today parameter(Date). Is it possible to do that in one query? ``` $qb = $this->createQueryBuilder('i'); $qb->innerJoin('i.type', 'it'); $qb->andWhere('it.name = :type'); $qb->andWhere('i.createdAt < :today'); // i.createdAt == datetime and :today parameter is a date ```

Original source