MySQL by year, Quarter, month, week, day SQL statistics query

Source: Internet
Author: User
Tags month name

I. Annual ENQUIRY Enquiry data for the yearSELECT * fromblog_articleWHERE  Year(From_unixtime (Blogcreatetime))=  Year(Curdate ()) Second, query quarterly data query data with quarter numberSELECTArticleID, Quarter (from_unixtime (' Blogcreatetime ')) from' blog_article ' other parts of the same previous section: Querying data for the quarterSELECT * fromblog_articleWHEREQuarter (From_unixtime (blogcreatetime))=Quarter (Curdate ()) Third, query monthly data statistics this month (MySQL)Select *  fromBookingwhere Month(Booking_time)=Month(Curdate ()) and  Year(Booking_time)=  Year(Curdate ()) This week's stats (MySQL)Select *  fromSpf_bookingwhere Month(Booking_time)=Month(Curdate ()) andWeek (Booking_time)=Week (Curdate ()) Four, time period n days recordWHERETo_days (now ())-To_days (Time field)<=N Records of the daywhereDate (Time field)=Date (now ()) orwhereTo_days (Time field)=To_days (now ()); query one week:Select *  from Table   whereDate_sub (Curdate (), INTERVAL7  Day)<=date (column_time); one months of enquiry:Select *  from Table whereDate_sub (Curdate (), INTERVAL INTERVAL1 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'  andDate_format (Birthday,'%m-%d')<= '07-08'; Statistics quarterly data, table Time fields: SavetimeGroup  byConcat (Date_format (Savetime,'%Y'), Floor(Date_format (Savetime,'%m')+2)/3)) orSelect  Year(Savetime)*Ten+((MONTH(Savetime)-1) DIV3)+1,Count(*) fromyourtableGroup  by  Year(Savetime)*Ten+((MONTH(Savetime)-1) DIV3)+1Five, group query1, Annual grouping2, monthly grouping3, GROUP by year first, and then by monthly group4, grouped by monthSELECT Count(ArticleID), Date_format (From_unixtime (' Blogcreatetime '),'%y%m') sdate from' Blog_article 'Group  bysdate Results:Count(ArticleID) sdate -     0901 One     09025     09036     09042     09051     0907 A     09086     0909 One     09103     0911Other methods of reference: I want to do a statistic, the database is MySQL, the number of daily, weekly, monthly records to build the table when the time to add a field to indicate the date, and then check the SQL manual ...Select Count(*) from`Table`where' Date '='{one day}'Select Count(*) from`Table`whereDate_format (' Date ','%V')='{a week}'Select Count(*) from`Table`whereDate_format (' Date ','%c')='{a month}'Another way:Select Count(*) fromProjectswhereEditdate>= '2007-11-9 00:00:00'  andEditdate<='2007-11-9 24:00:00'; The third method: Weekly SQL CodeselectCount(*) asCnt,week (editdate) asWeekflg fromProjectswhere  Year(editdate)= - Group  byWEEKFLG Monthly SQL CodeselectCount(*) asCntMonth(editdate) asMonthflg fromProjectswhere  Year(editdate)= - Group  byMONTHFLG Daily SQL CodeselectCount(*) asCnt fromProjectsGroup  bythe Date_format (date, format) function in date (editdate) 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: The following is the parameter description for the function:%S%s two digits in the form of seconds (xx, on, . . ., -)%I two digits in the form of points (xx, on, . . ., -)%H two digits in the form of hours, -Hoursxx, on, . . ., at)%H%I two digits in the form of hours, AHours on, Geneva, . . ., A)%K digital form of the hour, -Hours0,1, . . ., at)%L digital form of the hour, AHours1,2, . . ., A)%T -hours of Time form (hh:mm:s s)%R Ahours of Time form (Hh:mm:ss AM or hh:mm:ss PM)%p AM or P M%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 indicates the number of days in the month (xx, on, . . ., to)%The E number represents the number of days in the month (1,2, . . ., to)%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, the), where Sunday is the first day of the week%U Week (0,1, the), where Monday is the first day of the week%M Month name (January, February, ..., December)%b Abbreviated month name (January, February, ..., December)%The month represented by the M two digits ( on, Geneva, . . ., A)%The month represented by the C number (1,2, . . ., A)%Y Four-bit number represents the year%y two-bit number represents the year%%Direct value "%”

MySQL by year, Quarter, month, week, day SQL statistics query

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.