Symfony2 / Doctrine2 – filter with now

Problem: how do you get all entities with a date/timestamp before or after the current date time?

Solution: doctrine query – now with parameter

In SQL, you would write

SELECT * FROM page WHERE mydatetime > NOW()

This is not possible with doctrine2, that is used in symfony2.

Workaround: introduce a parameter that stores the now value.

$this->getEntityManager()
   ->createQuery('SELECT p FROM AcmeBundle:Page p WHERE NOT p.mydatetime IS NULL and p.mydatetime <= :now ORDER BY p.mydatetime ASC')
   ->setParameter('now', new \DateTime('now'))
   ->getResult()

This statement returns all page entities that have a mydatetime value that is in the past. If the timestamp is not set, they will also be filtered and not returned.