Symfony2 Doctrine query builder use date function

Question

how to use datetime functions in doctrine query builder,the below sql query need convert doctrine query(DQL).

SELECT SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, StartTime, EndTime)) + COUNT(*)) * 60) as hours FROM table_name

The below one i have tried

$qb = $emClient->createQueryBuilder()->select('SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, en.startTime, swenendTime)) + COUNT(*)) * 60) as hours')
            ->from('AppBundle:MyEntity', 'en')
$result = $qb->getQuery()->getArrayResult();

But i got error undefined keyword at column 0,7

Thanks in advance

score 3 · Answer 1 · answered Mar 13 '18 at 07:15

Doctrine supports following built-in functions http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#functions-operators-aggregates

So either you implement your own functions or use plain SQL query $emClient->getConnection()->executeQuery()

score 1 · Answer 2 · answered Mar 13 '18 at 09:32

In order to use these function either you need to implement them on your own if they don't exist as @Denis Alimov suggested or you can use already implemented function with Doctrine Extensions Bundle. The last option is get all the data needed and perform some operations on PHP side. You can also execute PURE sql and map it to doctrine with ResultSetMappingBuilder

Symfony2 Doctrine query builder use date function

2 Answers2