Group by day using the timestamp field

Question

My table schema looks like this:

+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field              | Type         | Null | Key | Default           | Extra                       |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| id                 | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name               | varchar(50   | NO   |     | 0                 |                             |
| modified           | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created            | timestamp    | NO   |     | CURRENT_TIMESTAMP |                             |
+--------------------+--------------+------+-----+-------------------+-----------------------------+

I want to get a count of the names and group by number of names modified by day at the moment I can only group by the full date including the timestamp eg:

SELECT name, count(*) FROM mytable GROUP BY modified

Thanks in advance.

Answer 1

Use MySQL DATE() function to extract the date from the timestamp:

SELECT name, count(*) FROM mytable GROUP BY DATE(mytable.modified);

The DATE() function extracts the date value from a date or datetime expression.

Answer 2

Dependant upon what you mean by day, you have a few options; Here's your cheat sheet.

DAYNAME(date) for the day of the week (Mon-Sun)

DAYOFMONTH(date) for the day of the month (1-31)

DAYOFWEEK(date) for the day of the week (1-7)

DAYOFYEAR(date) for the day of the year (1-365)

Edit:

If you want to group by the entire date (as opposed to a particular day), ignoring the time you can use

DATE_FORMAT(date, format)

The full list of format specifiers can be found at the above link, but what you'll probably need is:

Date_FORMAT(date, '%Y-%m-%d')

This will format the date as 'YYYY-MM-DD' and you can group by that.

Answer 3

You can use this query or a version of it SELECT DATE_FORMAT(modified,"%Y-%m-%d") as date_string, count(1) FROM mytable group by date_string;