MySQL is grouped by time

MySQL formatted date: date_format (date, format)

Date: Time fields format: Date format
Format the date value according to the format string:

%s,%s two digits in seconds (00, 01, ..., 59)%I,%I two digits in the form of points (00, 01, ..., 59)%H two digits in the form of hours, 24 hours (00,01, ..., 23)%h two digits in the form of hours, 12 hours (01,02, ..., 12)%k digital form of hours, 24 hours (0,1, ..., 23)%l digital form of hours, 12 hours (1, 2, ..., 12)%T 24hours of Time form (HH:MM:SS)%r 12hours of Time form (Hh:mm:ss AM or hh:mm:ss PM)%P AM or PM%W name of each day of the week (Sunday, Monday, ..., Saturday)%a abbreviation for the name of each day of the week (Sun, Mon, ..., Sat)%d two digits for the number of days in the month (XX,,..., 31)%e number representation of days in the month (1, 2, ..., 31)%D English suffix indicates the number of days in the month (1st, 2nd, 3rd,... )%w represents the number of days in a week in numbers (0 = Sunday, 1=monday, ..., 6 =Saturday)%j represents the number of days in a year in three digits (001, 002, ..., 366)%u week (0, 1, 52), where Sunday is the first day of the week%u week (0, 1, 52), where Monday is the first day of the week%M Month name (January, February, ..., December)%b Abbreviated month name (January, February,...., December)%m Two digit month (01, 02, ..., 12)%c number represents the month (1, 2, ...., 12)%Y Four-bit number represents the year%y two-bit number represents the yearPercent direct value "%"

1. By Month:
Select SUM (total_amount) as Total, date_format (stat_date, '%y-%m ') from Week_report WHERE ' stat_date ' between ' 2016-11-02 ' and ' 2017-04-30 ' GROUP by Date_format (Stat_date, '%y-%m ');
Select SUM (total_amount) as Total,date_format (stat_date, '%y-%m ') from Week_report WHERE ' stat_date ' between ' 2016-12-11 ' and ' 2016-12-22 ' GROUP by Date_format (Stat_date, '%y-%m ');
Get the data that is aggregated by month group.

Concat () connection string

--month
Select CONCAT (Year (stat_date), ' _ ', date_format (stat_date, '%m ')) months, sum (total_amount) as Count_amount, sum (total_ New_user) as Count_new_user, sum (da_active_user) as count_active_user from xxx
WHERE ' stat_date ' between ' 2016-01-02 ' and ' 2017-05-30 ' Group by months;

--Quarterly
Select CONCAT (Year (stat_date), ' _ ', Quarter (stat_date)) Qu,sum (Total_amount) as Count_amount, sum (total_new_user) as Count_new_user, SUM (da_active_user) as count_active_user from xxx
WHERE ' stat_date ' between ' 2016-01-02 ' and ' 2017-05-30 ' group by Qu;

--Zhou
Select CONCAT (Year (stat_date), ' _ ', date_format (stat_date, '%u ')) Weeks,sum (Total_amount) as Count_amount, sum (total_ New_user) as Count_new_user, sum (da_active_user) as count_active_user from xxx
WHERE ' stat_date ' between ' 2016-01-02 ' and ' 2017-05-30 ' Group by weeks;

-Day
Select CONCAT (Year (stat_date), ' _ ', date_format (stat_date, '%m '), ' _ ', date_format (stat_date, '%d ')) days, sum (total_ Amount) as Count_amount, sum (total_new_user) as Count_new_user, sum (da_active_user) as count_active_user from xxx
WHERE ' stat_date ' between ' 2016-01-02 ' and ' 2017-05-30 ' Group by Days;

Aggregated by year, statistics:
Select SUM (Mymoney) as Totalmoney, COUNT (*) as sheets from MyTable Group by Date_format (Col, '%Y ');
Summary by month, statistics:
Select SUM (Mymoney) as Totalmoney, COUNT (*) as sheets from MyTable Group by Date_format (Col, '%y-%m ');
Summary by quarter, statistics:
Select SUM (Mymoney) as Totalmoney,count (*) as sheets from MyTable Group by concat (Date_format (col, '%Y '), Floor (date_form At (Col, '%m ') +2)/3);
Select SUM (Mymoney) as Totalmoney,count (*) as sheets from MyTable Group by concat (Date_format (col, '%Y '), Floor (date_form At (Col, '%m ') +2)/3);
By hour:
Select SUM (Mymoney) as Totalmoney,count (*) as sheets from MyTable Group by Date_format (Col, '%y-%m-%d%H ');
Query this year's data:
SELECT * FROM MyTable WHERE year (From_unixtime (my_time)) = year (Curdate ())
Number of quarters with query data included:
SELECT ID, Quarter (from_unixtime (my_time)) from MyTable;
Query the data for this quarter:
SELECT * FROM MyTable WHERE quarter (From_unixtime (my_time)) = Quarter (Curdate ());
Monthly statistics:
SELECT * FROM MyTable where month (my_time1) = month (Curdate ()) and year (my_time2) = year (Curdate ())
This week's stats:
SELECT * FROM MyTable where month (my_time1) = month (Curdate ()) and week (my_time2) = Week (Curdate ())
N Days Record:
WHERE To_days (now ())-to_days (Time field) <=n

MySQL is grouped by time