#year_month

[postitcode]

MySql: Querying data by month

I realize that it is a common mistake to query monthly data using GROUP BY MONTH(date). It might seem to work but if your date data span more than a single year, you would actually group new and old year data of the same month together.

To avoid such a circumstances, you could instead use YEAR_MONTH as it would return a unique month signature for the different year. Take a look at the query snippet below.

By Month Query =================

SELECT COUNT(*) FROM table GROUP BY EXTRACT(YEAR_MONTH FROM timestamp)