mysql按照时间统计数据

1、按天进行统计

数据库时间格式

·

SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d') AS days,COUNT(1) AS num FROM 表名
GROUP BY days;

%Y-%m-%d格式一定要和数据库时间格式对应

2、按小时统计

SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H') AS days,COUNT(1) AS num FROM 表名
GROUP BY days;

3、按分统计

SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H:%i') AS days,COUNT(1) AS num FROM 表名
GROUP BY days;

4、按秒统计

SELECT DATE_FORMAT(`字段名`, '%Y-%m-%d %H:%i:%S') AS days,COUNT(1) AS num FROM 表名
GROUP BY days;

5、按天查询数据

select * from itops_message_center where  DATE_FORMAT(publish_time,'%Y-%m-%d') = '2022-06-14'

6、统计本周数据及对应个数

SELECT DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
count(1) countNumber
FROM aa
WHERE YEARWEEK(date_format(createtime, '%Y-%m-%d')) = YEARWEEK(now())
GROUP BY dateTime;

7、统计本周数据及对应个数，没有的自动补0

select
       d.date, IFNULL(T.countNumber, 0) countNumber
from (
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) + 0 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 1 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 2 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 3 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 4 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 5 DAY) as date
         UNION ALL
         select date_sub(curdate(), INTERVAL WEEKDAY(curdate()) - 6 DAY) as date
     ) d
         left join(
    select
          DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
               COUNT(1)                            countNumber
    from aa
    group by DATE_FORMAT(createtime, '%Y-%m-%d')
) T on T.dateTime = d.date
GROUP BY d.date;

8、统计当月的数据

select DATE_FORMAT(createtime, '%Y-%m-%d') dateTime,
count(1) countNumber
    from aa
where
    DATE_FORMAT(createtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
GROUP BY dateTime;

9、统计当月数据，不全的补0

select aa.DAY dateTime,
       (select count(*)
        from task_log t
        where substr(t.createtime, 1, 10) = aa.DAY and t.is_deleted = 0) as countNumber
from (SELECT date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY),
                      INTERVAL (cast(help_topic_id AS signed INTEGER) - 1) DAY
                 ) DAY
      FROM mysql.help_topic
      WHERE help_topic_id < DAY(last_day(curdate()))
      ORDER BY help_topic_id) aa;