MySQL Query GROUP BY day / month / year

Question

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!

Answer 1

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.

Answer 2

You can do this simply Mysql DATE_FORMAT() function in GROUP BY. You may want to add an extra column for added clarity in some cases such as where records span several years then same month occurs in different years.Here so many option you can customize this. Please read this befor starting. Hope it should be very helpful for you. Here is sample query for your understanding

SELECT
    COUNT(id),
    DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY,
    DATE_FORMAT(record_date, '%Y-%m') AS MONTH,
    DATE_FORMAT(record_date, '%Y') AS YEAR,

FROM
    stats
WHERE
    YEAR = 2009
GROUP BY
    DATE_FORMAT(record_date, '%Y-%m-%d ');

Answer 3

If you want to group by date in MySQL then use the code below:

 SELECT COUNT(id)
 FROM stats
 GROUP BY DAYOFMONTH(record_date)

Hope this saves some time for the ones who are going to find this thread.

Answer 4

I prefer to optimize the one year group selection like so:

SELECT COUNT(*)
  FROM stats
 WHERE record_date >= :year 
   AND record_date <  :year + INTERVAL 1 YEAR;

This way you can just bind the year in once, e.g. '2009', with a named parameter and don't need to worry about adding '-01-01' or passing in '2010' separately.

Also, as presumably we are just counting rows and id is never NULL, I prefer COUNT(*) to COUNT(id).

Answer 5

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);

Answer 6

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.