mysql - 使用时间戳字段按天分组

Question

我的表架构如下所示:

+--------------------+--------------+------+-----+-------------------+-----------------------------+
| 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 |                             |
+--------------------+--------------+------+-----+-------------------+-----------------------------+

我想获得名称的计数，并按当天修改的名称数量进行分组，目前我只能按包括时间戳的完整日期进行分组，例如:

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

提前致谢。

Answer 1

使用 MySQL DATE() 函数从时间戳中提取日期:

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

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