This article brings you an introduction to the method of MySQL statistical data by time (code example). It has certain reference value. Friends in need can refer to it. I hope it will be useful to you. Helps.
When doing database statistics, it is often necessary to count data based on year, month, and day, and then use echarts
to create visualization effects.
Database: MySQL (Recommended tutorial: MySQL Tutorial)
Thinking
The prerequisite for making statistics according to the time dimension is that the database must have retention time information. It is recommended to use the datetime
type that comes with MySQL
to record time.
`timestamp` datetime DEFAULT NULL,
The main function for processing time and date in MySQL is DATE_FORMAT(date,format)
. The available parameters are as follows
Format | Description |
---|---|
%a | Abbreviated week name |
%b | Abbreviated month name |
%c | Month, value |
%D | Day of the month with English prefix |
%d | Day of the month, numerical value (00-31) |
%e | Day of the month, numerical value (0-31) |
%f | Microseconds |
%H | Hours (00-23) |
%h | hours(01-12) |
%I | hours(01-12) |
%i | Minute, value (00-59) |
%j | Day of the year (001-366) |
%k | Hours(0-23) |
%l | Hours(1-12) |
%M | Month name |
%m | Month, value (00-12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm: ss AM or PM) |
%S | seconds (00-59) |
%s | Seconds(00-59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | week(00-53) Sunday is the first day of the week |
%u | week(00-53) Monday Is the first day of the week |
%V | week(01-53) Sunday is the first day of the week, use |
%v | Week (01-53) Monday is the first day of the week, and %x uses |
%W | Week name |
%w | Day of the week (0=Sunday, 6=Saturday) |
% Year is the first day of the week, 4 digits, and %v uses | |
year, 4 digits | |
year, 2 digits | |
Note | : When it comes to daily statistics, you need to use%j | , and if
Involves getting the current time, you can get it through now()
or sysdate()
.
SELECT SYSDATE() FROM DUAL; SELECT NOW() FROM DUAL;
Just use
group by
to query according to actual needs.
The table structure to be counted is as follows: <pre class="brush:php;toolbar:false">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`)
)</pre>
# 当日 SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%j') = DATE_FORMAT(now(),'%j') ORDER BY count desc; # 当周 SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%u') = DATE_FORMAT(now(),'%u') ORDER BY count desc; # 当月 SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%m') = DATE_FORMAT(now(),'%m') ORDER BY count desc; # 当年 SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%Y') = DATE_FORMAT(now(),'%Y') ORDER BY count desc;
# 按日 SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%j') # 按周 SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%u') # 按月 SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%m') # 按年 SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%Y')
Press
action and time dimension at the same time Statistics# 按日 SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%j') # 按周 SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%u') # 按月 SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%m') # 按年 SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%Y')
The above are the more commonly used time statistics. For more time dimensions, you can refer to the above parameter table for similar processing.
The above is the detailed content of Introduction to the method of MySQL statistics by time (code example). For more information, please follow other related articles on the PHP Chinese website!