Summary of MySQL time statistics methods

Summary of MySQL time statistics methods

When doing database statistics, you often need to collect data based on year, month, and day, and then use echarts to create visualizations.

Database: MySQL

Ideas

The premise of statistics based on the time dimension is that the database needs to have retained 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

Format describe
%a Abbreviated day of the week
%b Abbreviated month name
%c Month, value
%D Days of the month with English prefixes
%d Day of the month, numeric value (00-31)
%e Day of the month, numeric (0-31)
%f Microseconds
%H Hours (00-23)
%h Hours (01-12)
%I Hours (01-12)
%i Minute, numeric value (00-59)
%j Day of the year (001-366)
%k Hours (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

Note: When it comes to daily statistics, %j is required. If %d, %e, %w are used, the same values ​​in different months/weeks will be counted together.

When it comes to getting the current time, you can use now() or sysdate().

SELECT SYSDATE() FROM DUAL;

SELECT NOW() FROM DUAL;

You can use group by query according to actual needs.

Conclusion <br /> 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`)

)

Count the number of actions of different categories within the time range

# SELECT action on the day, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%j') = DATE_FORMAT(now(),'%j') ORDER BY count desc;

# This week SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%u') = DATE_FORMAT(now(),'%u') ORDER BY count desc;

# Current month 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;

Count the number of time dimensions of a certain classification action

# By day SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%j')

# By week SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%u')

# By month SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%m')

# By year SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%Y')

Statistics by action and time dimensions at the same time

# By day SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%j')

# By week SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%u')

# By month SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%m')

# By year 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 parameter table above for similar processing.

You may also be interested in:
  • Mysql example of converting birth date into age and grouping and counting the number of people
  • Reasons why MySQL 8.0 statistics are inaccurate
  • A brief discussion on MySQL count of rows
  • A small Shell script to accurately count the number of rows in each Mysql table
  • MySQL implements multi-table association statistics (subquery statistics) example
  • Laravel implements the method of counting MySQL data by month, day or hour
  • sqlserver/mysql statistics of continuous time period data by day, hour, minute [recommended]
  • Detailed explanation of MySQL to obtain statistical data for each day and each hour of a certain period of time
  • PHP+MySQL to achieve daily data statistics optimization operation example within a period of time
  • PHP advertising click statistics code (php+mysql)
  • Overview of MySQL Statistics
  • A brief analysis of MySQL cardinality statistics

<<:  Detailed Analysis of Event Bubbling Mechanism in JavaScript

>>:  Detailed steps for installing and debugging MySQL database on CentOS7 [Example]

Recommend

In html table, set different colors and widths for each cell

It is recommended that you do not set the width, h...

About the problem of running git programs in jenkins deployed by docker

1. First, an error message is reported when assoc...

In-depth understanding of JavaScript callback functions

Table of contents Preface Quick Review: JavaScrip...

Analysis of different MySQL table sorting rules error

The following error is reported when MySQL joins ...

Detailed explanation of the solution to Tomcat's crash when double-clicking startup.bat

As a programmer who has just learned Tomcat, this...

Json string + Cookie + localstorage in JS

Table of contents 1.Json string 1.1Json Syntax 1....

Sorting out some common problems encountered in CSS (Hack logo/fixed container/vertical centering of images)

1. IE browser mode Hack logo 1. CSS hack logo Copy...

Quickly install MySQL5.7 compressed package on Windows

This article shares with you how to install the M...

Example code for implementing bottom alignment in multiple ways with CSS

Due to the company's business requirements, t...

List rendering instructions for efficient development of Vue front-end

v-for directive Speaking of lists, we have to men...

Example method to find keywords and their preceding and following information in Linux logs

In daily work, we often need to view logs. For ex...

WML tag summary

Structure related tags ---------------------------...

How to clear the timer elegantly in Vue

Table of contents Preface optimization Derivative...

CSS to achieve the like card effect in the lower right corner of the web page (example code)

Effect To implement HTML, first prepare a clean H...

Use of Vue3 table component

Table of contents 1. Ant Design Vue 1. Official w...