Group by using month, year in doctrine2

Question

How to write a following SQL query in doctrine2 as DQL .

SELECT COUNT(id)  
FROM stats  
WHERE YEAR(record_date) = 2009  
GROUP BY YEAR(record_date), MONTH(record_date)

i.e i would like to group by results based on month,year of datetime field stored in MySQL table.

Answer 1

In DQL you could also group by month, year, day etc with SUBSTRING.

For example - group by month (datetime format Y-m-d H:i:s):

SELECT p, SUBSTRING(p.date, 6, 2) as month
FROM Entity p
GROUP BY month