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

Detailed explanation of the relationship between image format and design in web design

Why do I want to organize the content in this area...

JavaScript exquisite snake implementation process

Table of contents 1. Create HTML structure 2. Cre...

SQL Practice Exercise: Online Mall Database User Information Data Operation

Online shopping mall database-user information da...

Detailed explanation of HTML tables

Function: data display, table application scenari...

Explanation of using if judgment conditions in sum and count functions when using SQL statements to collect data

First, let me give you an example (if you don’t w...

jQuery implements the bouncing ball game

This article shares the specific code of jQuery t...

CentOS 8 custom directory installation nginx (tutorial details)

1. Install tools and libraries # PCRE is a Perl l...

Sample code for nginx to achieve dynamic and static separation

1. Simple configuration of nginx's dynamic an...

JavaScript to implement a simple shopping form

This article shares the specific code of JavaScri...

Tutorial on how to modify the root password in MySQL 5.7

Version update, the password field in the origina...

The process of building lamp architecture through docker container

Table of contents 1. Pull the centos image 2. Bui...

Detailed explanation of React setState data update mechanism

Table of contents Why use setState Usage of setSt...

How to solve the problem of MySQL query character set mismatch

Find the problem I recently encountered a problem...

Detailed explanation of the role of brackets in AngularJS

1. The role of brackets 1.1 Square brackets [ ] W...

Three steps to solve the IE address bar ICON display problem

<br />This web page production skills tutori...