Introduction to the method of MySQL statistics by time (code example)

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

with %X %Y %y: When it comes to daily statistics, you need to use , and if %d, %e, %w is used, the same values ​​in different months/weeks will be counted together.

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	%j

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.

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`)
  )

Different categories within the statistical time range

action# Number of

##

# 当日
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;

Statistics the number of time dimensions of a certain category

action

• # 按日
  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!