What is the method of aggregating statistics in Mysql date format?

Introduction

Time and date are commonly used grouping conditions and are often used in actual development to facilitate data statistics. In MySQL, we can use the date formatting function to convert the date into a string in a specified format, and then perform group statistics according to the required time granularity.

Preparation

Before we start, we need to prepare a test data table and insert some data to facilitate our subsequent experiments and tests.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test` (`name`, `created_at`) VALUES
('test1', '2022-01-01 00:00:00'),
('test2', '2022-01-02 00:00:00'),
('test3', '2022-01-02 12:00:00'),
('test4', '2022-01-03 00:00:00'),
('test5', '2022-01-07 00:00:00'),
('test6', '2022-01-08 00:00:00'),
('test7', '2022-01-09 00:00:00'),
('test8', '2022-01-10 00:00:00'),
('test9', '2022-01-14 00:00:00'),
('test10', '2022-01-15 00:00:00'),
('test11', '2022-01-16 00:00:00'),
('test12', '2022-01-17 00:00:00'),
('test13', '2022-02-01 00:00:00'),
('test14', '2022-02-02 00:00:00'),
('test15', '2022-02-03 00:00:00'),
('test16', '2022-03-01 00:00:00'),
('test17', '2022-03-02 00:00:00'),
('test18', '2022-03-03 00:00:00'),
('test19', '2022-04-01 00:00:00'),
('test20', '2022-04-02 00:00:00'),
('test21', '2022-04-03 00:00:00'),
('test22', '2022-05-01 00:00:00'),
('test23', '2022-05-02 00:00:00'),
('test24', '2022-05-03 00:00:00');

Here we create a test table named test, containing three fields id, name and created_at . Among them, created_at represents the date and time type field that records the creation time. We inserted some test data, including data from January to May 2022.

Implementation Principle

When we need to group statistics based on dates, MySQL provides many built-in dates Functions, such as YEAR(), MONTH(), WEEK(), DAY(), HOUR(), etc. These functions group data by date and count the corresponding quantities.

For this requirement, we need to group by date and calculate the data for 7 days, 4 weeks and 3 months. Therefore, we need to use a combination of date formatting and date functions.

First, we need to format the date into the corresponding format. You can do this using the DATE_FORMAT() function, which takes two parameters: date and format string. The date can be formatted as "yyyy-MM-dd" as shown below, for example:

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM table_name;

Actual operation

Statistics by day

Next, we need to Group dates and count quantities. This can be achieved using the GROUP BY clause and the corresponding date function. For example, we can count the daily quantity according to date grouping, as follows:

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date, COUNT(*) AS count FROM table_name GROUP BY formatted_date;

Count by week

SELECT DATE_FORMAT(date_column, '%x-%v') AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;

or

SELECT CONCAT(YEAR(date_column), '-', WEEK(date_column)) AS formatted_week, COUNT(*) AS count FROM table_name GROUP BY formatted_week;`

Count by month

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

or

SELECT CONCAT(YEAR(date_column), '-', MONTH(date_column)) AS formatted_month, COUNT(*) AS count FROM table_name GROUP BY formatted_month;

The above is the detailed content of What is the method of aggregating statistics in Mysql date format?. For more information, please follow other related articles on the PHP Chinese website!