Shulou(Shulou.com)05/31 Report--

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!

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.