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

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

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

  Copy after login

  Just use group by to query according to actual needs.

Conclusion

• The table structure to be counted is as follows: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">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`) )</pre><div class="contentsignin">Copy after login</div></div>

Different categories within the statistical time range

##

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

Copy after login

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

  Copy after login
  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 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!