How to query by month in mysql

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

Mysql group query method by month:

select DATE_FORMAT(createtime,'%Y-%m'),count(*) from test where user =8 group by DATE_FORMAT(createtime,'%Y-%m');//按月统计数据
select DATE_FORMAT(createtime,'%Y-%m-%d'),count(*) from test where user =8 group by DATE_FORMAT(createtime,'%Y-%m-%d');//按天统计数据

+---------------------------------+----------+
| 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 the date value according to the format string. The following modifiers can be used in the format string:

• %M month name (January......December)

• %W week name (Sunday...Saturday)

• %D The day of the month with an English prefix (1st, 2nd, 3rd, etc.)

• %Y year, number, 4 digits

• ##%y year, number, 2 digits

• %a abbreviated day of the week name (Sun...... Sat)

• %d Number of days in the month, number (00......31)

• %e Number of days in the month, number (0 ……31)

• %m month, number (01……12)

• %c month, number (1……12)

• %b Abbreviated month name (Jan...Dec)

##%j Number of days in a 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 The number of days in a week (0=Sunday...6=Saturday)

• %U The number of days in a 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 literal "%".

More related free learning recommendations:

mysql tutorial(Video)

The above is the detailed content of How to query by month in mysql. For more information, please follow other related articles on the PHP Chinese website!