How to get the difference between 2 dates with Dql

Question

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

I tried the following code but that did not 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 your function will take 3 parameters in your request.

In Doctrine2, you should use one of the following functions, which works best for you instead of the 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. 

In conclusion, your query should look something like this:

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