MySQL SQL statement by annual, quarterly, Monthly, weekly, and daily statistics query

This article describes some MySQL SQL statements for querying, including by year, quarter, month, week, day and so on, the need for friends, you can refer to.

I. Annual ENQUIRY
Querying data for the year

SELECT *
From Blog_article
WHERE year (From_unixtime (blogcreatetime)) = year (Curdate ())

Second, query quarterly data
Number of quarters with query data included

SELECT ArticleID, Quarter (from_unixtime (' Blogcreatetime '))
From ' Blog_article '

Query the data for the quarter

SELECT *
From Blog_article
WHERE Quarter (From_unixtime (blogcreatetime)) = Quarter (Curdate ())

Third, query monthly data
Monthly Statistics (MySQL)

SELECT * FROM booking where month (booking_time) =
Month (Curdate ()) and year (booking_time) = year (Curdate ())

Weekly Stats (MySQL)

SELECT * FROM spf_booking where month (booking_time) =
Month (Curdate ()) and week (booking_time) = Week (Curdate ())

Iv. time period

N Days of recording

WHERE To_days (now ())-To_days (Time field) <= N

Record of the day

Where date (Time field) =date (now ())
---or
Where To_days (Time field) = To_days (now ());

Query one week:

SELECT * FROM table where Date_sub (Curdate (), INTERVAL 7 day) <= DATE (column_time);

Query one months:

SELECT * FROM table where Date_sub (Curdate (), INTERVAL INTERVAL 1 MONTH) <= DATE (column_time);

Query ' 06-03 ' to ' 07-08 ' for all birthdays during this time period:

Select * from user Where
Date_format (Birthday, '%m-%d ') >= ' 06-03 ' and date_format (birthday, '%m-%d ')
<= ' 07-08 ';

Statistics quarterly data, table Time field: Savetime

Group BY Concat (Date_format (savetime, '%Y '), Floor ((Date_format (savetime, '%m ') +2)/3))
---or
Select year (savetime) *10+ ((MONTH (Savetime)-1) DIV 3) +1,count (*)
From yourtable
Group by year (Savetime) *10+ ((MONTH (Savetime)-1) DIV 3) +1;

Five, group query
1. Group of the Year
2. Monthly Group
3, first group by year, and then by the monthly group
4. GROUP BY month

SELECT count (ArticleID), Date_format (From_unixtime (' blogcreatetime '), '%y%m ') sdate from ' Blog_article ' GROUP by sdate

Results:
Count (ArticleID) sdate
17 0901
11 0902
5 0903
6 0904
2 0905
1 0907
12 0908
6 0909
11 0910
3 0911

Other methods of reference:
To do a statistic, the database is MySQL, statistics the number of records per day, weekly, monthly
When you build a table, add a field to represent the date.

Method 1,

Select COUNT (*) from ' table ' where ' date ' = ' {One day} '
Select COUNT (*) from ' table ' where Date_format (' Date ', '%V ') = ' {a week} '
Select COUNT (*) from ' table ' where Date_format (' Date ', '%c ') = ' {a month} '

Method 2,

Select COUNT (*) from projects where Editdate >= ' 2007-11-9 00:00:00 ' and editdate <=
' 2007-11-9 24:00:00 ';

Method 3,
of the Week

Select COUNT (*) as Cnt,week (editdate) as WEEKFLG from projects where year (editdate)
=2007 GROUP BY WEEKFLG

Monthly

Select COUNT (*) as Cnt,month (editdate) as MONTHFLG from projects where year
(editdate) =2007 GROUP by MONTHFLG

Every day

Select COUNT (*) as CNT from Projects Group by date (editdate)

The Date_format (date, format) function in MySQL formats a date or date and time value, based on the format string, to return a result string.
You can also use Date_format () to format a date or datetime value to get the format you want. Format the date value according to the format string:

Parameter description of the function:
%s,%s two digits in seconds (00, 01, ..., 59)
%i two digits in the form of points (00, 01, ..., 59)
%H two-digit hours, 24 hours (00,01, ..., 23)
%h,%I 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 24-hour time form (hh:mm:s s)
%r 12-hour time format (hh:mm:ss AM or Hh:mm:ss PM)
%p AM or P M
%W the name of each day of the week (Sunday, Monday, ..., Saturday)
%a the abbreviation of the name of each day of the week (Sun, Mon, ..., Sat)
%d two digits indicates the number of days in the month (00, 01, ..., 31)
%e number representation of days in the month (1, 2, ..., 31)
%d The English suffix indicates the number of days in the month (1st, 2nd, 3rd, ...). ）
%w number of days in the week (0 = Sunday, 1=monday, ..., 6=saturday)
%j three-digit number of days in the year (001, 002, ..., 366)
% U Week (0, 1, 52), where Sunday is the first day of the week
%u weeks (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)
The month of%c numbers (1, 2, ..., 12)
%Y Four-digit year
%y Two-digit year
Percent direct value "%"

MySQL SQL statement by annual, quarterly, Monthly, weekly, and daily statistics query