Shulou(Shulou.com)06/01 Report--

Xiaobian to share with you mysql by month group query method, I hope you read this article after the harvest, let us discuss it together!

mysql query method by month: format date value according to format string, syntax is [DATE_FORMAT(date,format)], where [ %M] is the name of month.

mysql query method grouped by month:

select DATE_FORMAT (createtime,'%Y-%m'),count(*) from test where user =8 group by DATE_FORMAT (createtime,'%Y-% m');//select DATE_FORMAT by Month (createtime,'%Y-%m-%d'),count(*) from test where user =8 group by DATE_FORMAT (createtime,'%Y-%m-% d');//Statistics by day +------------------------------------+------| DATE_FORMAT(createtime,'%Y-%m') | count(*) |+---------------------------------+----------+| 2015-05 | 1 || 2015-06 | 1 || 2015-07 | 5 || 2015-08 | 1 |+---------------------------------+----------+4 rows in set

DATE_FORMAT(date,format)

Format date values according to the format string. The following modifiers can be used in format strings:

%M Month Name (January…December)

%W Name of week (Sunday…Saturday)

%D Date of month with English prefix (1st, 2nd, 3rd, etc.)

%Y year, number, 4 digits

%y year, number, 2 digits

%a Abbreviated week name (Sun... Sat)

%d Days in month, number (00……31)

%e Days in month, number (0……31)

%m Month, Number (01……12)

%c month, number (1……12)

%b Abbreviated month name (Jan…Dec)

%j Days of the year (001……366)

%H hours (00……23)

%k hours (0……23)

%h hours (01……12)

%I hours (01……12)

%l hours (1……12)

%i minutes, number (00……59)

%r Time, 12 hours (hh:mm:ss [AP]M)

%T time, 24 hours (hh:mm:ss)

%S seconds (00……59)

%s seconds (00……59)

%p AM or PM

%w Days of the week (0=Sunday …6=Saturday)

%U Week (0……52), where Sunday is the first day of the week

%u Week (0……52), where Monday is the first day of the week

%%A word "%".

After reading this article, I believe you have a certain understanding of mysql by month grouping query method, want to know more relevant knowledge, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.