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

Source: Internet
Author: User

Description

12345 select year ( ' 2014-10-29 ' //2014 select month ( ' 2014-10-29 ' //10 Select day ( ' 2014-10-29 ' //29 select quarter ( ' 2014-10-29 ' //4    quarter select dayofweek ( ' 2014-10-29 '   //4    week

I. Annual ENQUIRY
Querying data for the year

123 SELECT *FROM blog_articleWHERE year( FROM_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))

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

12 SELECT ArticleId, quarter( FROM_UNIXTIME( `BlogCreateTime` ) )FROM `blog_article`

Other previous sections: Querying data for the quarter

123 SELECT *FROM blog_articleWHERE quarter( FROM_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))

Third, query monthly data
Monthly Statistics (MySQL)

12 select* from booking where month(booking_time) =month(curdate()) and year(booking_time) = year(curdate())

Weekly Stats (MySQL)

12 select* from spf_booking where month(booking_time) =month(curdate()) and week(booking_time) = week(curdate())

Iv. time period
N Days of recording

1 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= N

Record of the day

1 where date(时间字段)=date(now())

Or

1 where to_days(时间字段) = to_days(now());

Query one week

1 select* from table   where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

Query one months

1 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:

12 Select * From user WhereDATE_FORMAT(birthday,‘%m-%d‘) >= ‘06-03‘and DATE_FORMAT(birthday,‘%m-%d‘)<= ‘07-08‘;

Statistics quarterly data, table Time field: Savetime

1 group by concat(date_format(savetime, ‘%Y ‘),FLOOR((date_format(savetime, ‘%m ‘)+2)/3))

Or

123 selectYEAR(savetime)*10+((MONTH(savetime)-1) DIV 3) +1,count(*)from yourTablegroup 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

1 SELECT count(ArticleId), date_format(FROM_UNIXTIME( `BlogCreateTime`),‘%y%m‘) sdate  FROM `blog_article` group by sdate

Results:

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

Other method references:
I want to do a statistic, the database is MySQL, the daily, weekly, monthly number of records
when the table is created by adding a field to indicate the date, and then check the SQL manual ...

123 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 ) = ' {a month} '

Another way:

12 selectcount( * ) from projects where editdate >= ‘2007-11-9 00:00:00‘and editdate <=‘2007-11-9 24:00:00‘;

The third method:
of the Week

12 SQL codeselect count(*) as cnt,week(editdate) as weekflg from projects where year(editdate)=2007 group by weekflg

Monthly

12 SQL codeselect count(*) as cnt,month(editdate) as monthflg from projects where year(editdate)=2007 group by monthflg

Every day

1 SQL codeselect count(*) as cnt from projects group by date(editdate)

MySQL date_format (date, format) functions 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 according to format string
Date value:
Here is the parameter description for the function:

12345678910111213141516171819202122232425 %S, %s 两位数字形式的秒( 00,01, . . ., 59)%i 两位数字形式的分( 00,01, . . ., 59)%H 两位数字形式的小时,24 小时(00,01, . . ., 23)%h, %I 两位数字形式的小时,12 小时(01,02, . . ., 12)%k 数字形式的小时,24 小时(0,1, . . ., 23)%l 数字形式的小时,12 小时(1, 2, . . ., 12)%T 24 小时的时间形式(hh : mm : s s)%r 12 小时的时间形式(hh:mm:ss AM 或hh:mm:ss PM)%p AM 或P M%W 一周中每一天的名称( Sunday, Monday, . . ., Saturday)%a 一周中每一天名称的缩写( Sun, Mon, . . ., Sat)%d 两位数字表示月中的天数( 00, 01, . . ., 31)%e 数字形式表示月中的天数( 1, 2, . . ., 31)%D 英文后缀表示月中的天数( 1st, 2nd, 3rd, . . .)%w 以数字形式表示周中的天数( 0 = Sunday, 1=Monday, . . ., 6=Saturday)%j 以三位数字表示年中的天数( 001, 002, . . ., 366)% U 周(0, 1, 52),其中Sunday 为周中的第一天%u 周(0, 1, 52),其中Monday 为周中的第一天%M 月名(January, February, . . ., December)%b 缩写的月名( January, February, . . ., December)%m 两位数字表示的月份( 01, 02, . . ., 12)%c 数字表示的月份( 1, 2, . . ., 12)%Y 四位数字表示的年份%y 两位数字表示的年份%% 直接值“%”

Note: The article by the SEO technical http://www.618sale.com/to edit, reprint need to indicate the source.

from:http://hfreeze.blog.51cto.com/1639680/1569278

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.