MySQL Query GROUP BY day / month / year

Is it possible I make a simple query to count how many records I have in a determined period of time like a Year, month or day, having a TIMESTAMP field, like:

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

Or even:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

To have a monthly statistic.

Thanks!

 Answers

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

---

I tried using the 'WHERE' statement above, I thought its correct since nobody corrected it but I was wrong; after some searches I found out that this is the right formula for the WHERE statement so the code becomes like this:

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

---

If your search is over several years, and you still want to group monthly, I suggest:

version #1:

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY DATE_FORMAT(record_date, '%Y%m')

version #2 (more efficient):

SELECT SQL_NO_CACHE YEAR(record_date), MONTH(record_date), COUNT(*)
FROM stats
GROUP BY YEAR(record_date)*100 + MONTH(record_date)

I compared these versions on a big table with 1,357,918 rows (innodb), and the 2nd version appears to have better results.

version1 (average of 10 executes): 1.404 seconds 
version2 (average of 10 executes): 0.780 seconds 

(SQL_NO_CACHE key added to prevent MySQL from CACHING to queries.)

---

If you want to filter records for a particular year (e.g. 2000) then optimize the WHERE clause like this:

SELECT MONTH(date_column), COUNT(*)
FROM date_table
WHERE date_column >= '2000-01-01' AND date_column < '2001-01-01'
GROUP BY MONTH(date_column)
-- average 0.016 sec.

Instead of:

WHERE YEAR(date_column) = 2000
-- average 0.132 sec.

The results were generated against a table containing 300k rows and index on date column.

As for the GROUP BY clause, I tested the three variants against the above mentioned table; here are the results:

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY YEAR(date_column), MONTH(date_column)
-- codelogic
-- average 0.250 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY DATE_FORMAT(date_column, '%Y%m')
-- Andriy M
-- average 0.468 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY EXTRACT(YEAR_MONTH FROM date_column)
-- fu-chi
-- average 0.203 sec.

The last one is the winner.

---

The following query worked for me in Oracle Database 12c Release 12.1.0.1.0

SELECT COUNT(*)
FROM stats
GROUP BY 
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR  FROM TIMESTAMP);

---

Complete and simple solution with similarly performing yet shorter and more flexible alternative currently active:

SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

---

.... group by to_char(date, 'YYYY') --> 1989

.... group by to_char(date,'MM') -->05

.... group by to_char(date,'DD') --->23

.... group by to_char(date,'MON') --->MAY

.... group by to_char(date,'YY') --->89