按 年,月,周,日 统计sql

/*查询2小时前的数据*/
select * from tableName WHERE
        create_time < DATE_SUB(NOW(), INTERVAL 2 HOUR)

SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) < DATE_SUB(CURDATE(), INTERVAL 7 DAY);

SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) > DATE_SUB(CURDATE(), INTERVAL 7 DAY);

 

/*按天统计*/

SELECT
    count(id) countNum,
    DATE(create_time) createTime
FROM
    rd_track_info
GROUP BY
    DATE(create_time)
ORDER BY
    DATE(create_time) DESC;


/*按周统计*/
SELECT
    count(id) countNum,
    WEEK(create_time) createTime
FROM
    rd_track_info
GROUP BY
    WEEK(create_time)
ORDER BY
    WEEK(create_time) DESC;


/*按月统计*/
SELECT
    count(id) countNum,
    MONTH(create_time) createTime
FROM
    rd_track_info
GROUP BY
    MONTH(create_time)
ORDER BY
    MONTH(create_time) DESC;


/*按季度统计*/
SELECT
    count(id) countNum,
    QUARTER(create_time) createTime
FROM
    rd_track_info
GROUP BY
    QUARTER(create_time)
ORDER BY
    QUARTER(create_time) DESC;

/*按年统计*/
SELECT
    count(id) countNum,
    YEAR(create_time) createTime
FROM
    rd_track_info
GROUP BY
    YEAR(create_time)
ORDER BY
    YEAR(create_time) DESC;

create_time为时间格式(字段名create_time 根据自己表字段修改,数据库中存为201610071202）

SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;

SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;

SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months;

create_time为时间戳格式(字段名create_time 根据自己表字段修改,数据库中存为1474959640）：

SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;

SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;

SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months;