mysql将时间戳转换为年月日格式进行查询

  create_time时间格式
        SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
create_time时间戳格式
        SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
举例：
<!--	查询主管最近30天注册用户数   -->
		SELECT DATE_FORMAT(a.create_time, '%Y%m%d') AS days, COUNT(*) AS userCount
		FROM sssss a
		WHERE  a.dept_id = 1
			AND a.create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
			AND a.recommender_user_id IN (
				SELECT DISTINCT b.recommender_user_id
				FROM sssssss  b
					JOIN ssssss c ON b.recommender_user_id = c.user_id
				WHERE c.distri_user_id = #{userId}
			)
		GROUP BY days;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17