MySQL Query GROUP BY day / month / year

mysql sql date datetime group-by
Fernando Barrocal picture Fernando Barrocal · Feb 3, 2009 · Viewed 670.2k times · Source

Is it possible to 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!

Answer

codelogic picture codelogic · Feb 3, 2009 
GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

Related questions

Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

Read More
Error in MySQL when setting default value for DATE or DATETIME

I'm running MySql Server 5.7.11 and this sentence: updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' is not working. Giving the error: ERROR 1067 (42000): Invalid default value for 'updated' But the following: updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00' …

Read More
Get a list of dates between two dates

Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values: 2009-01…

Read More