Expression mysql NOW() in Doctrine QueryBuilder

Question

How to use the expression mysql NOW() in doctrine querybuilder?

Bearable

Answer 1

In Doctrine2 you have to use one of the following instead of NOW().
This:

CURRENT_TIMESTAMP()

Or:

...
createQuery(...'WHERE x.date = :now')
->setParameter('now', new \DateTime('now'))
...

If you want only time or only date use one of those: CURRENT_TIME() and CURRENT_DATE()

Documentation can be found here.

Flesh

Documentation link reports not found, please update. – Coverlet

This CURRENT_TIMESTAMP() thing has never has worked for me. Could you provide an example of how that is used? I don't wan't to use the web server's time, rather the database. I always end up writing raw SQL (still binding params) and forgoing the query builder. – Gerous

Also notice 'now' is default param for DateTime, so new \DateTime() is enough. – Earflap

Answer 2

Using query builder it would look like this:

$qb
    ->select('B')
    ->from('RandomBundle:Banana', 'B')
    ->where(
        $qb->expr()->gt('B.expiresAt', ':now')
    )
    ->setParameter('now', '\'CURRENT_TIMESTAMP()\'');

Note: extra quotes on parameter set is required to get CURRENT_TIMESTAMP() function working.

Or simply

$qb
    ->select('B')
    ->from('RandomBundle:Banana', 'B')
    ->where(
        $qb->expr()->gt('B.expiresAt', 'CURRENT_TIMESTAMP()')
    );

Customary

In the second example, you can use the shorter B.expiresAt > CURRENT_TIMESTAMP() comparison directly inside the where call instead of using a query builder expression – Barbur

I assume it was desired to use database's time, not some frontend web server's. Alternate options are to use a resultset mapper/native query, or to extend Doctrine and write your own Doctrine\ORM\Query\AST\Functions. – Gerous