CREATE TABLE `histories` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into histories values (null, 1, '2011-05-01 00:00:00', '2011-05-01 00:00:00');
insert into histories values (null, 1, '2011-05-01 00:00:00', '2011-05-01 00:00:00');
insert into histories values (null, 1, '2011-05-02 00:00:00', '2011-05-02 00:00:00');
insert into histories values (null, 1, '2011-05-02 00:00:00', '2011-05-02 00:00:00');
insert into histories values (null, 1, '2011-05-03 00:00:00', '2011-05-03 00:00:00');
select date(created) as date, count(*) from histories group by date;
-- select substring(created, 1,10) as date, count(*) from histories group by date;
+------------+----------+
| date | count(*) |
+------------+----------+
| 2011-05-01 | 2 |
| 2011-05-02 | 2 |
| 2011-05-03 | 1 |
+------------+----------+