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

Introduction to MySQL statement comments

MySQL supports three types of comments: 1. From t...

Problems and solutions when installing and using VMware

The virtual machine is in use or cannot be connec...

How to use nginx as a load balancer for mysql

Note: The nginx version must be 1.9 or above. Whe...

Learn the basics of nginx

Table of contents 1. What is nginx? 2. What can n...

Manjaro installation CUDA implementation tutorial analysis

At the end of last year, I replaced the opensuse ...

Implementation of vue3.0+vant3.0 rapid project construction

Table of contents 1. Project Construction 2. Vue3...

Solution to PHP not being able to be parsed after nginx installation is complete

Table of contents Method 1 Method 2 After install...

How to install and configure SSH service in Ubuntu 18.04

Install ssh tool 1. Open the terminal and type th...

Record a troubleshooting record of high CPU usage of Tomcat process

This article mainly records a tomcat process, and...

React Native JSI implements sample code for RN and native communication

Table of contents What is JSI What is different a...

How to run sudo command without entering password in Linux

The sudo command allows a trusted user to run a p...

JavaScript regular verification password strength implementation method

exhibit design Password strength analysis The pas...

Solution to the Docker container cannot be stopped and deleted

Find the running container id docker ps Find the ...

Interaction in web design: A brief discussion on paging issues

Function: Jump to the previous page or the next p...

IE8 compatibility notes I encountered

1. IE8's getElementById only supports id, not ...