Table of Contents
-
- 1. Group by day
-
- 1.1, dayofyear, dayofmonth, dayofweek
- 1.2, format function
- 2. Group by week
-
- 2.1, week function
- 2.2, weekofyear function
- 2.3, weekday function
- 2.4, dayname function
- 3. Group by month
-
- 3.1, month function
- 3.2, DATE_FORMAT function
- 4. Group by season
-
- 4.1, quarter function
- 5. Group by year
-
- 5.1, year function
- 5.2, DATE_FORMAT function
- epilogue
1. Group by day
1.1, dayofyear, dayofmonth, dayofweek
- dayofyear(date) The function returns the day of the year in which the date is located, and the range is 1 ~ 366
- dayofmonth(date) The function returns the day of the month in which the date is located, and the range is 1 ~ 31
- dayofweek(date) The function returns the day of the week in which the date is located, and the range is 1 ~ 7
Query statement
select dayofmonth(transtime) as transDay, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59' group by transDay order by transDay asc;
Query results
+ ---------- + ---------- + | transDay | transNum | + ---------- + ---------- + | 1 | 1704 | | 2 | 985 | | 3 | 723 | | 4 | 606 | | 5 | 581 | | 6 | 1051 | | 7 | 1257 | | 8 | 637 | | 9 | 1049 | | 10 | 559 | | 11 | 724 | | 12 | 964 | | 13 | 1139 | | 14 | 2542 | | 15 | 5957 | | 16 | 3185 | | 17 | 543 | | 18 | 507 | | 19 | 854 | | 20 | 849 | | 21 | 2216 | | 22 | 3788 | | 23 | 2498 | | 24 | 693 | | 25 | 597 | | 26 | 756 | | 27 | 854 | | 28 | 1583 | | 29 | 2180 | | 30 | 1855 | | 31 | 744 | + ---------- + ---------- + 31 rows in set (0.05 sec)
It should be noted that if it is dayofmonth or dayofweek The time spans months or weeks, and the data of multiple months or weeks will be merged together. If you want to separate, you can use the following formatting method.
1.2, format function
- DATE_FORMAT(date, ‘%Y-%m-%d’) The function returns the formatted date according to the specified expression, including the year, month and day
Query statement
select DATE_FORMAT(transtime, '%Y-%m-%d') as transDay, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59' group by transDay order by transDay asc;
Query results
+ ------------ + ---------- + | transDay | transNum | + ------------ + ---------- + | 2016-10-01 | 1704 | | 2016-10-02 | 985 | | 2016-10-03 | 723 | | 2016-10-04 | 606 | | 2016-10-05 | 581 | | 2016-10-06 | 1051 | | 2016-10-07 | 1257 | | 2016-10-08 | 637 | | 2016-10-09 | 1049 | | 2016-10-10 | 559 | | 2016-10-11 | 724 | | 2016-10-12 | 964 | | 2016-10-13 | 1139 | | 2016-10-14 | 2542 | | 2016-10-15 | 5957 | | 2016-10-16 | 3185 | | 2016-10-17 | 543 | | 2016-10-18 | 507 | | 2016-10-19 | 854 | | 2016-10-20 | 849 | | 2016-10-21 | 2216 | | 2016-10-22 | 3788 | | 2016-10-23 | 2498 | | 2016-10-24 | 693 | | 2016-10-25 | 597 | | 2016-10-26 | 756 | | 2016-10-27 | 854 | | 2016-10-28 | 1583 | | 2016-10-29 | 2180 | | 2016-10-30 | 1855 | | 2016-10-31 | 744 | + ------------ + ---------- + 31 rows in set (0.08 sec)
2. Group by week
2.1, week function
- week(date) The function returns the date which is the week of the year, every week starts from Sunday, and the value range is 0 ~ 53
Query statement
select week(transtime) as transWeek, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' group by transWeek order by transWeek asc;
Query results
+ ----------- + ---------- + | transWeek | transNum | + ----------- + ---------- + | 0 | 2 | | 1 | 8 | | 2 | 9 | | 3 | 1 | | 4 | 4 | | 5 | 7 | | 6 | 4 | | 7 | 9 | | 8 | 7 | | 9 | 2 | | 10 | 21 | | 11 | 18 | | 12 | 19 | | 13 | 34 | | 14 | 31 | | 15 | 17 | | 16 | 130 | | 17 | 261 | | 18 | 230 | | 19 | 494 | | 20 | 452 | | 21 | 485 | | 22 | 590 | | 23 | 684 | | 24 | 580 | | 25 | 620 | | 26 | 370 | | 27 | 155 | | 28 | 721 | | 29 | 747 | | 30 | 659 | | 31 | 775 | | 32 | 843 | | 33 | 897 | | 34 | 926 | | 35 | 975 | | 36 | 975 | | 37 | 1048 | | 38 | 393 | | 39 | 4145 | | 40 | 5840 | | 41 | 12934 | | 42 | 11942 | | 43 | 9161 | | 44 | 9102 | | 45 | 8284 | | 46 | 6150 | | 47 | 5825 | | 48 | 6374 | | 49 | 6929 | | 50 | 4366 | | 51 | 3858 | | 52 | 5855 | + ----------- + ---------- + 53 rows in set (0.12 sec)
2.2, weekofyear function
- weekofyear(date) The function returns the date of the week of the year, each week starts from Monday, and the value range is 1 ~ 53
Query statement
select weekofyear(transtime) as transWeek, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' group by transWeek order by transWeek asc;
Query results
+ ----------- + ---------- + | transWeek | transNum | + ----------- + ---------- + | 1 | 11 | | 2 | 6 | | 4 | 5 | | 5 | 6 | | 6 | 5 | | 7 | 9 | | 8 | 7 | | 9 | 1 | | 10 | 22 | | 11 | 17 | | 12 | 23 | | 13 | 34 | | 14 | 28 | | 15 | 16 | | 16 | 199 | | 17 | 194 | | 18 | 299 | | 19 | 510 | | 20 | 458 | | 21 | 492 | | 22 | 571 | | 23 | 709 | | 24 | 546 | | 25 | 640 | | 26 | 278 | | 27 | 257 | | 28 | 723 | | 29 | 720 | | 30 | 710 | | 31 | 786 | | 32 | 813 | | 33 | 921 | | 34 | 957 | | 35 | 1002 | | 36 | 932 | | 37 | 1032 | | 38 | 327 | | 39 | 5064 | | 40 | 5904 | | 41 | 15070 | | 42 | 11255 | | 43 | 8518 | | 44 | 9203 | | 45 | 7836 | | 46 | 5448 | | 47 | 6608 | | 48 | 5934 | | 49 | 6639 | | 50 | 4160 | | 51 | 3887 | | 52 | 5173 | | 53 | 3 | + ----------- + ---------- + 52 rows in set (0.12 sec)
2.3, weekday function
- weekday(date) Returns the day of the week, note that Monday is 0 and Tuesday is 1. . . Sunday is 6
Query statement
select weekday(transtime) as transWeek, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59' group by transWeek order by transWeek asc;
Query results
+ ----------- + ---------- + | transWeek | transNum | + ----------- + ---------- + | 0 | 3262 | | 1 | 2434 | | 2 | 3155 | | 3 | 3893 | | 4 | 7598 | | 5 | 14266 | | 6 | 9572 | + ----------- + ---------- + 7 rows in set (0.05 sec)
At the same time, if you feel unintuitive and you want to get the name, you can use dayname(date) instead of weekday(date) , the result is as follows
2.4, dayname function
- dayname(date) Returns the day of the week: MONDAY, TUESDAY…SUNDAY
Query statement
select dayname(transtime) as transWeek, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-10-01 00:00:00' and '2016-10-31 23:59:59' group by transWeek order by transWeek asc;
Query results
+ ----------- + ---------- + | transWeek | transNum | + ----------- + ---------- + | Friday | 7598 | | Monday | 3262 | | Saturday | 14266 | | Sunday | 9572 | | Thursday | 3893 | | Tuesday | 2434 | | Wednesday | 3155 | + ----------- + ---------- + 7 rows in set (0.08 sec)
3. Group by month
3.1, month function
- month(date) The function returns the month corresponding to the date, ranging from 1 to 12
Query statement
select month(transtime) as transMonth, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' group by transMonth order by transMonth asc;
Query results
+ ------------ + ---------- + | transMonth | transNum | + ------------ + ---------- + | 1 | 25 | | 2 | 27 | | 3 | 83 | | 4 | 449 | | 5 | 1893 | | 6 | 2611 | | 7 | 2411 | | 8 | 3811 | | 9 | 5334 | | 10 | 44180 | | 11 | 30140 | | 12 | 24004 | + ------------ + ---------- + 12 rows in set (0.12 sec)
At the same time, if you want to get the name, you can use MONTHNAME(date) instead of month(date) , the result is as follows:
+ ------------ + ---------- + | transMonth | transNum | + ------------ + ---------- + | April | 449 | | August | 3811 | | December | 24004 | | February | 27 | | January | 25 | | July | 2411 | | June | 2611 | | March | 83 | | May | 1893 | | November | 30140 | | October | 44180 | | September | 5334 | + ------------ + ---------- + 12 rows in set (0.20 sec)
It should be noted that if it is a new year, the data of many years will be merged together, and it is not known how much a certain month is in each year. If you want to separate, you can use the following The formatting method.
3.2, DATE_FORMAT function
- DATE_FORMAT(date, ‘%Y-%m’) The function returns the formatted date according to the specified expression, including the year and month
Query statement
select DATE_FORMAT(transtime, '%Y-%m') as transMonth, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' group by transMonth order by transMonth asc;
Query results
+ ------------ + ---------- + | transMonth | transNum | + ------------ + ---------- + | 2016-01 | 25 | | 2016-02 | 27 | | 2016-03 | 83 | | 2016-04 | 449 | | 2016-05 | 1893 | | 2016-06 | 2611 | | 2016-07 | 2411 | | 2016-08 | 3811 | | 2016-09 | 5334 | | 2016-10 | 44180 | | 2016-11 | 30140 | | 2016-12 | 24004 | + ------------ + ---------- + 12 rows in set (0.20 sec)
4. Group by season
4.1, quarter function
- quarter(date) The function returns the quarter corresponding to the date, ranging from 1 to 4
Query statement
select quarter(transtime) as quarterNo, count(*) as transNum from tb_inf_otherbiz where transtime between '2016-01-01 00:00:00' and '2016-12-31 23:59:59' group by quarterNo order by quarterNo asc;
Query results
+ ----------- + ---------- + | quarterNo | transNum | + ----------- + ---------- + | 1 | 135 | | 2 | 4953 | | 3 | 11556 | | 4 | 98324 | + ----------- + ---------- + 4 rows in set (0.12 sec)
5. Group by year
5.1, year function
- year(date) The function returns the year corresponding to the date
Query statement
select year(transtime) as transYear, count(*) as transNum from tb_inf_otherbiz where transtime between '2015-01-01 00:00:00' and '2022-12-31 23:59:59' group by transYear order by transYear asc;
Query results
+ ----------- + ---------- + | transYear | transNum | + ----------- + ---------- + | 2015 | 6 | | 2016 | 114968 | | 2017 | 66703 | | 2018 | 2738 | | 2019 | 1853 | | 2020 | 651 | | 2021 | 40 | + ----------- + ---------- + 7 rows in set (0.19 sec)
5.2, DATE_FORMAT function
- DATE_FORMAT(date, ‘%Y’) The function returns the formatted date according to the specified expression, including the year
Query statement
select DATE_FORMAT(transtime, '%Y') as transYear, count(*) as transNum from tb_inf_otherbiz where transtime between '2015-01-01 00:00:00' and '2022-12-31 23:59:59' group by transYear order by transYear asc;
Query results
+ ----------- + ---------- + | transYear | transNum | + ----------- + ---------- + | 2015 | 6 | | 2016 | 114968 | | 2017 | 66703 | | 2018 | 2738 | | 2019 | 1853 | | 2020 | 651 | | 2021 | 40 | + ----------- + ---------- + 7 rows in set (0.19 sec)
Conclusion
The operations in this article are all based on the mysql8.0 version. Understanding the functions of mysql will make these queries easier.