查询本周的
SELECT
DATE_FORMAT( create_time, '%Y%m%d' ) weeks,
count( create_time ) count
FROM
alarm
WHERE
YEARWEEK( date_format( create_time, '%Y-%m-%d' ), 1 ) = YEARWEEK( now(), 1 )
GROUP BY
weeks;
**YEARWEEK( date_format( create_time, '%Y-%m-%d' ), 1 ) = YEARWEEK( now(), 1 )**
这个1 不能少 因为mysql 好像默认上周日也属于本周 所以要排除
SELECT
DATE_FORMAT( create_time, '%Y%m%d' ) dateTime,
count( create_time ) count
FROM
alarm
WHERE
DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' )
GROUP BY
dateTime;
按月查询得
当天得
SELECT
DATE_FORMAT( create_time, '%Y%m%d%H' ) dateTime,
count( 1 ) count
FROM
alarm
WHERE
to_days( create_time ) = to_days(
now())
GROUP BY
dateTime;
近七天的
SELECT
DATE_FORMAT( create_time, '%Y%m%d' ) dateTime,
count( 1 ) count
FROM
alarm
WHERE
DATE_SUB( CURDATE(), INTERVAL 6 DAY ) `<=` date( create_time )
GROUP BY
dateTime;
INTERVAL 6 DAY 这个要写6 因为写7会有8条数据
参考链接
https://www.cnblogs.com/benefitworld/p/5832897.html 这个写的很全
https://www.cnblogs.com/xr210/p/12686912.html