How to compare datetime field from Doctrine2 with a date?

Question

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

Answer 1

It is also possible to use built-in function DATE_DIFF(date1, date2) which returns difference in days. Check docs

$result = $this->createQueryBuilder('l')
    ->where('DATE_DIFF(l.startDate, CURRENT_DATE()) = 0')

Answer 2

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