[MySQL] MySQL groups statistical data by year / month / day / week

1. Statistics

We can use date_format() Function formatting time , Then group

For example, there is a student list , The structure is as follows

--  By year 
select date_format(create_time, '%Y') years,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by years;
--  monthly 
select date_format(create_time, '%Y-%m') months,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by months;
--  By week 
select date_format(create_time, '%Y-%u') weeks,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by weeks;
--  By day 
select date_format(create_time, '%Y-%m-%d') days,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by days;

If you don't want to use date_format function , You can use the corresponding year()/month()/week()/day() Function substitution

2. Place holder

date_format() You need to pass in a specific placeholder ,mysql Common placeholders can refer to the following table