MySQL method summary of statistical data by time

When doing database statistics, it is often necessary to make statistics according to the year, month and day, and then cooperate with echarts to make visual effects.

Database: MySQL

Train of thought

The premise of statistics according to time dimension is to keep time information in the database. It is recommended to use datetime type of MySQL to record time.

`timestamp` datetime DEFAULT NULL,

The function for processing time and date in MySQL is mainly DATE_FORMAT (date, format). The available parameters are as follows

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是1周的第1天
%u	周 (00-53) 星期1是1周的第1天
%V	周 (01-53) 星期日是1周的第1天，与 %X 使用
%v	周 (01-53) 星期1是1周的第1天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期6）
%X	年，其中的星期日是周的第1天，4 位，与 %V 使用
%x	年，其中的星期1是周的第1天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

Note:% j is required when it comes to daily statistics, and if% d,% e,% w are used, the same values in different months/weeks will be counted from 1.

When it comes to getting the current time, you can get it through now () or sysdate ().

SELECT SYSDATE() FROM DUAL;

SELECT NOW() FROM DUAL;

Use group by query according to actual needs.

Conclusion
The table structure to be counted is as follows:

CREATE TABLE `apilog` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `username` varchar(64) DEFAULT NULL,

 `action` varchar(64) DEFAULT NULL,

 `params` text,

 `result` text,

 `timestamp` datetime DEFAULT NULL,

 PRIMARY KEY (`id`)

)

The number of action in different categories in the statistical time range

#  Day 

SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%j') = DATE_FORMAT(now(),'%j') ORDER BY count desc;

#  Current week 

SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%u') = DATE_FORMAT(now(),'%u') ORDER BY count desc;

#  Current month 

SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%m') = DATE_FORMAT(now(),'%m') ORDER BY count desc;

#  Year 

SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%Y') = DATE_FORMAT(now(),'%Y') ORDER BY count desc;

Count the number of time dimensions of a classification action

#  By day 

SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%j')

#  By week 

SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%u')

#  Monthly 

SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%m')

#  Annual 

SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%Y')

At the same time, according to action and time dimension statistics

#  By day 

SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%j')

#  By week 

SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%u')

#  Monthly 

SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%m')

#  Annual 

SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%Y')

The above is more commonly used time statistics, more time dimensions, you can refer to the above parameter table similar processing.