How can I use SQL's YEAR(), MONTH() and DAY() in Doctrine2?

Question

I want to perform a query which would look like this in native SQL:

SELECT     AVG(t.column) AS average_value FROM     table t WHERE     YEAR(t.timestamp) = 2013 AND     MONTH(t.timestamp) = 09 AND     DAY(t.timestamp) = 16 AND     t.somethingelse LIKE 'somethingelse' GROUP BY     t.somethingelse; 

If I am trying to implement this in Doctrine's query builder like this:

$qb = $this->getDoctrine()->createQueryBuilder(); $qb->select('e.column AS average_value')    ->from('MyBundle:MyEntity', 'e')    ->where('YEAR(e.timestamp) = 2013')    ->andWhere('MONTH(e.timestamp) = 09')    ->andWhere('DAY(e.timestamp) = 16')    ->andWhere('u.somethingelse LIKE somethingelse')    ->groupBy('somethingelse'); 

I get the error exception

[Syntax Error] line 0, col 63: Error: Expected known function, got 'YEAR'

How can I implement my query with Doctrines query builder?

Notes:

• I know about Doctrine's Native SQL. I've tried this, but it leads to the problem that my productive and my development database tables have different names. I want to work database agnostic, so this is no option.
• Although I want to work db agnostic: FYI, I am using MySQL.
• There is way to extend Doctrine to "learn" the YEAR() etc. statements, e.g. as seen here. But I am looking for a way to avoid including third party plugins.

Answer 1

You can add Doctrine extension so you can use the MySql YEAR and MONTH statement by adding this configuration if you're on Symfony:

doctrine:     orm:         dql:             string_functions:                 MONTH: DoctrineExtensions\Query\Mysql\Month                 YEAR: DoctrineExtensions\Query\Mysql\Year 

now you can use the MONTH and YEAR statements in your DQL or querybuilder.

Note: The extension supports MySQL, Oracle, PostgreSQL and SQLite.