how to get the difference between 2 dates with Dql

Question

How can I get the difference between 2 dates with Dql?

I have tried the following code, but that didn't work.

$query =$this->_em->createQuery('select a,DATEDIFF(d,\'now\',a.date) from ABundle:Abonment a where d==1');

How can I solve this?

Answer 1

From this source http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html#dql-functions:

DATE_DIFF(date1, date2) - Calculate the difference in days between date1-date2, and in your query your function is taken 3 parameters.

In Doctrine2 you have to use one of the following functions that fits better on you instead of now() function:

CURRENT_DATE() - Return the current date
CURRENT_TIME() - Returns the current time
CURRENT_TIMESTAMP() - Returns a timestamp of the current date and time.

On conclusion your query should by something like:

$query =$this->_em->createQuery('select a, DATE_DIFF(CURRENT_DATE(), a.date) as days from ABundle:Abonment a where days = 1');