mysql查询年月日的数据

# 查询每天的数据
SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d')  AS dateTime,
addtime
FROM admin_log
GROUP BY DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d') ;



本月每天   DATE_FORMAT(时间字段,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m');
SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d')  AS dateTime,
addtime
FROM admin_log
 WHERE DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y%m')= DATE_FORMAT(CURDATE(),'%Y%m')
GROUP BY DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d') ;




# 查询每月的数据:by month
SELECT 
    DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m') AS month,
    DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-01') AS createDate,
    COUNT(*) AS countNumber
FROM project_case
GROUP BY month ORDER BY month ASC;


# 查询每年的数据
SELECT
    COUNT(1) AS countNumber,
    YEAR(DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d')) AS dateTime
FROM admin_log
GROUP BY YEAR(DATE_FORMAT(FROM_UNIXTIME(addtime),'%Y-%m-%d')) ;


时间为日期类型==================================================

# 查询每天的数据
SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(create_time,'%Y-%m-%d') AS dateTime
FROM project_case
GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d');

# 查询每周的数据
SELECT 
    DATE_FORMAT(create_time,'%Y-%u') AS week,
    DATE_FORMAT(DATE_SUB(create_time,INTERVAL DAYOFWEEK(create_time) - 2 DAY),'%Y-%m-%d') AS createDate,
    COUNT(*) AS countNumber
FROM project_case
GROUP BY week ORDER BY week ASC;

# 查询每月的数据:by month
SELECT 
    DATE_FORMAT(create_time,'%Y-%m') AS month,
    DATE_FORMAT(create_time,'%Y-%m-01') AS createDate,
    COUNT(*) AS countNumber
FROM project_case
GROUP BY month ORDER BY month ASC;

# 查询每年的数据
SELECT
    COUNT(1) AS countNumber,
    YEAR(create_time) AS dateTime
FROM project_case
GROUP BY dateTime;

参考：https://www.jianshu.com/p/3f25da4c9a07
          https://blog.csdn.net/VIP_1205169154/article/details/108552768