我有一个以addtime列为时间戳的用户表。我想按年、月、日获得用户组的数量。
table

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
)

数据

1,'user1','2018-01-02 10:01:01'
2,'user2','2018-01-02 10:11:01'
3,'user3','2018-01-03 10:01:01'
4,'user4','2018-01-03 10:11:01'
5,'user5','2018-01-03 10:21:01'
6,'user6','2018-01-03 10:41:01'
7,'user7','2018-01-05 10:01:01'
8,'user8','2018-01-05 10:11:01'
9,'user9','2018-01-05 10:21:01'

查询

SELECT DATE_FORMAT(addtime, '%Y-%m-%d') AS Date, COUNT(id) AS total FROM user WHERE addtime BETWEEN '2018-01-01 00:00:00' AND '2018-01-07 00:00:00' GROUP BY YEAR(addtime), MONTH(addtime), DAY(addtime);

查询仅在当天有用户时返回值。

2018-01-02, 2
2018-01-03, 4
2018-01-05, 3

我需要的结果

2018-01-01, 0
2018-01-02, 2
2018-01-03, 4
2018-01-04, 0
2018-01-05, 3
2018-01-06, 0