How does mysql get statistics for every day and every hour in a certain period of time
Editor to share with you how mysql gets the statistics of every day and every hour in a certain period of time. I hope you will get something after reading this article. Let's discuss it together.
Get statistics for each day
When doing a project, you need to analyze the project log. One of the requirements is to obtain the log data for each day within a given period of time. For example, to obtain the log data for each day during the period from 2018-02-02 09:18:36 to 2018-03-05 23:18:36, generally speaking, when you see this requirement, you will consider using a function to solve it, and use the sql statement directly.
SELECT DATE_FORMAT (trigger_time,'% Ymuri% mmi% d') triggerDay, COUNT (id) triggerCountFROM `job_qrtz_trigger_ log`where trigger_time BETWEEN '2018-02-02 09:18:36'AND' 2018-03-05 23:18:36'GROUP BY triggerDayORDER BY trigger_time
Query results:
Give a simple explanation to the sql above
First of all, the query field here, COUNT (id) triggerCount is the number of statistical data we need, and we can add the required fields according to the actual demand.
DATE_FORMAT (trigger_time,'% Ymuri% mmury% d') triggerDay
This is a date that formats a specific date into YYYY-mm-dd format. The format here is used for later grouping, so you can customize the format according to different requirements.
My requirement is to get the log data for each day, so I group it in a year-month-date format and get it through count (*).
If you get the amount of data for each month within a month, change the format to
DATE_FORMAT (trigger_time,'% YMY% m')
Of course, where conditions need to be able to match the format, not where conditions limited to dates, but you use month grouping.
The date output format of mysql is listed:
% an abbreviated week name
% b abbreviated month name
% c month, numeric
% D day of the month with English prefix
Day of% d month, numeric (00-31)
Day of% e month, numeric (0-31)
% f microseconds
% H hours (00-23)
% h hours (01-12)
% I hours (01-12)
% I minutes, value (00-59)
Days of% j year (001-366)
% k hours (0-23)
% l hours (1-12)
% M month name
% m month, numeric (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, used with% X
% v week (01-53) Monday is the first day of the week, used with% x
% W week name
% w days of the week (0 = Sunday, 6 = Saturday)
% X year, where Sunday is the first day of the week, 4 digits, used with% V
% x year, where Monday is the first day of the week, 4 digits, used with% v
% Y year, 4 digits
% y year, 2 digits
Get statistics for every hour of a day
Using HOUR function of Mysql to deal with
SELECT HOUR (trigger_time) as Hour,count (*) as Count FROM xxl_job_qrtz_trigger_log WHERE trigger_time BETWEEN '2018-02-05 01:18:36'AND' 2018-02-05 17 as Hour,count 18 SELECT HOUR (trigger_time) ORDER BY Hour (trigger_time)
The query results are as follows
After reading this article, I believe you have a certain understanding of "how mysql obtains the statistics of every day and every hour of a certain period of time". If you want to know more about it, please follow the industry information channel and thank you for your reading!
