31

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

Ricardo Viana
  • 341
  • 1
  • 3
  • 6

2 Answers2

53

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.

Benoit Esnard
  • 2,017
  • 2
  • 24
  • 32
Mats Rietdijk
  • 2,576
  • 3
  • 20
  • 25
  • Documentation link reports not found, please update. – wesamly Oct 25 '14 at 08:24
  • 1
    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. – ficuscr Feb 09 '15 at 08:07
  • 4
    Also notice `'now'` is default param for `DateTime`, so `new \DateTime()` is enough. – Jan Mares Jul 28 '16 at 15:17
9

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()')
    );
Aistis
  • 3,695
  • 2
  • 34
  • 34
  • 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 – Clamburger Dec 31 '15 at 15:02
  • 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](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html), or to extend Doctrine and write your own Doctrine\ORM\Query\AST\Functions. – ficuscr Oct 03 '17 at 19:58