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 query query data  select *from blog_articlewhere year (From_unixtime (blogcreatetime)) = year (Curdate ()) Second, query the number of quarterly data query According to the number of quarters  select ArticleID, Quarter (from_unixtime (' Blogcreatetime ')) from ' blog_article ' query data for the quarter  select * From Blog_articlewhere quarter (From_unixtime (blogcreatetime)) = Quarter (Curdate ()) third, monthly data query statistics (MySQL)  select * From booking where month (booking_time) =month (Curdate ()) and year (booking_time) = year (Curdate ()) Week statistics (MySQL)   SELECT * FROM spf_booking where month (booking_time) =month (Curdate ()) and week (booking_time) = Week (Curdate ()) Four, time period n days recorded  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 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 Wheredate_format (birthday, '%m-%d ') >= ' 06-03 ' and Date_format ( Birthday, '%m-%d ') <= ' 07-08 '; Statistics quarterly data, table Time fields: 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 Yourtablegroup by year (Savetime) *10+ ((MONTH (Savetime)-1) DIV 3) +1; Group Query   &NBSP;1, annual group   &NBSP;2, monthly group    3, first by year group, then by monthly group   &NBSP;4, group by month     select count (ArticleID), Date_format (From_unixtime (' Blogcreatetime '), '%y%m ') sdate  from ' blog_article ' GROUP by sdate Result: Count (ArticleID)     SDATE17     090111     09025      09036     09042     09051     090712 & nbsp   09086     090911     09103     0911 Other methods reference: Do a statistic, the database is MySQL, statistics out daily, weekly, The number of records per month when the table is built, add a field to indicate the date. Method 1, select Count (*) from ' table ' where ' date ' = ' {Some 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, weekly  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 MONTHF LG Daily  select COUNT (*) as CNT from Projects Group by date (editdate) in MySQL date_format (date, FORMAT) The function can format date or date and time values according to the format string and return the 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: The parameter description for the function:%s,%s, two-digit seconds (00,01, ...,%i), two-digit (00,01, ..., ()%H two-digit hours, 24 hours (00,01,. . ., 23°c)%h,%I Two digits in the form of hours, 12 hours (01,02, ..., 1)%k digital form of hours, 24 hours (0,1, ..., 12 hours)%l digital form of the hour, and 2, 24, ..., the%T of the Time form (hh:mm: s s)%r a 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 initials of each day of the week (Sun, Mon, ..., Sat)%d two digits indicate the number of days in the month (xx,%, ..., +)%e number as the number of days in the month (1, 2, ....)%d The English suffix indicates the number of days in the month (1st, 2nd, 3rd, ...). %w the 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 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 (in. 1, 2, ..., two) the month of%c (four-digit number) represents the year, which is the value of the year, which is represented by the number of years%Y, "%".

MySQL SQL statements by year, quarter, month, week, and day statistics query