Detailed explanation of MySQL to obtain statistical data for each day and each hour of a certain period of time

Get daily statistics

When doing a project, you need to analyze the project log. One of the requirements is to obtain the log data of each day in a given time period. For example, you need to obtain the log data of each day from 2018-02-02 09:18:36 to 2018-03-05 23:18:36. Generally, when you see this requirement, you consider using functions to solve it, and directly use SQL statements.

SELECT
	DATE_FORMAT(trigger_time, '%Y-%m-%d') triggerDay,
	COUNT(id) triggerCount
FROM
	`job_qrtz_trigger_log`
WHERE
	trigger_time BETWEEN '2018-02-02 09:18:36'
AND '2018-03-05 23:18:36'
GROUP BY triggerDay
ORDER BY trigger_time;

Query results:

A brief explanation of the above sql

First, query the field. Here, COUNT(id) triggerCount is the number of statistical data we need. You can add required fields according to actual needs.

DATE_FORMAT(trigger_time, '%Y-%m-%d') triggerDay

This is a date formatted into the format of YYYY-mm-dd. The format here is used for subsequent grouping, so you can customize the format according to different needs.

My requirement is to get the log data for each day, so I group them in the format of year-month-date and get them through count(*).

If you want to get the amount of data for each month in a certain month, change the format to

DATE_FORMAT(trigger_time, '%Y-%m')

Of course, the where condition needs to match the format. You cannot limit the where condition to the date and group by month.

The date output format of MySQL is listed below:

%a abbreviated day of the week
%b abbreviated month name
%c Month, numeric value
%D Day of the month with English prefix
%d Day of the month, numeric value (00-31)
%e Day of the month, numeric value (0-31)
%f microseconds
%H Hours (00-23)
%h Hour (01-12)
%I Hour (01-12)
%i Minute, value (00-59)
%j Day of the year (001-366)
%k hour (0-23)
%l hours (1-12)
%M Month name
%m Month, numeric 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, used with %X
%v Week (01-53) Monday is the first day of the week, used with %x
%W day of the week
%w day 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 each hour of a day

Use the HOUR function that comes with Mysql to process

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:18:36'
  GROUP BY HOUR(trigger_time) ORDER BY Hour(trigger_time);

The query results are as follows

The above is the detailed explanation and integration of MySQL to obtain statistical data for every day and every hour in a certain period of time introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!