Getting error while using interval in doctrine

Question

When I use below query (Doctrine 2), I was getting error, and can't use INTERVAL in query,

$qb->andWhere("(pv.appointment_date + INTERVAL 48 HOUR) >= UTC_TIMESTAMP()");

Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '48'"

Answer 1

Doctrine is an ORM which used DQL, it is not same as SQL. Not all functions in the sql are supported by doctine by default. DQL doesn't ships with the support for INTERVAL. For that you have to add user defined functions DQL User Defined Functions.

A complete set of function is available in this git repo DoctrineExtensions

And the above query will become DATE_ADD(pv.appointment_date, INTERVAL 48 HOUR) >= UTC_TIMESTAMP()

Answer 2

If you want to use INTERVAL (in Doctrine 2, DQL) on mysql comumn field, You can use as below,

$qb->andWhere("DATE_ADD(pv.appointmentDate,48,'hour') >= UTC_TIMESTAMP()");

It will print SQL as below,

...... DATE_ADD(p0_.appointmentDate, INTERVAL 48 HOUR) >= UTC_TIMESTAMP() .....