Shulou(Shulou.com)06/01 Report--

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

Database: MySQL

Train of thought

The premise of statistics according to the time dimension is that there is retention time information in the database. It is recommended to use the datetime type that comes with MySQL to record the time.

`timestamp` datetime DEFAULT NULL

In MySQL, the main function for dealing with time and date is DATE_FORMAT (date,format). The available parameters are as follows

Format description% an abbreviated week name% b abbreviated month name% c month, value% D day of month with English prefix% d month, value (00-31)% e-month day, value (0-31)% f microsecond% H-hour (00-23)% h-hour (01-12)% I-hour (01-12)% I minute Value (00-59)% j year day (001-366)% k hour (0-23)% l hour (1-12)% M month name% m month, value (00-12)% pAM or PM%r time 12-hour (hh:mm:ss AM or PM)% S seconds (00-59)% s seconds (00-59)% T time, 24-hours (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 With% X using% v week (01-53) Monday is the first day of the week, with% x using% W week name% w week day (0 = Sunday, 6 = Saturday)% X year, where Sunday is the first day of the week, 4 digits, and% V using% x year, where Monday is the first day of the week, 4 digits, and% v using% Y year, 4 digits, 2 digits

Note: when it comes to daily statistics, you need to use% j, but if you use% d,% e,% w, then the same values in different months / weeks will be counted together.

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

SELECT SYSDATE () FROM DUAL;SELECT NOW () FROM DUAL

Just use the group by query according to the 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`))

Count the number of different categories of action in the time range

# SELECT action, COUNT (id) count FROM apilog WHERE DATE_FORMAT (`timestamp`,'% j') = DATE_FORMAT (now (),'% j') ORDER BY count desc;# of the current week 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;# current 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 classified action

# SELECT action, DATE_FORMAT (`timestamp`,'% j'), COUNT (id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT (`timestamp`,'% j') # weekly 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')

Statistics are based on both action and time dimensions

# Daily SELECT action, DATE_FORMAT (`timestamp`,'% j'), COUNT (id) count FROM apilog GROUP BY action, DATE_FORMAT (`timestamp`,'% j') # weekly 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') # by year SELECT action DATE_FORMAT (`timestamp`,'% Y'), COUNT (id) count FROM apilog GROUP BY action, DATE_FORMAT (`timestamp`,'% Y')

These are the more commonly used time statistics. For more time dimensions, you can refer to the above parameter table for similar processing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.