Home >Database >Mysql Tutorial >How to query by month in mysql

How to query by month in mysql

coldplay.xixi
coldplay.xixiOriginal
2020-10-13 14:43:288695browse

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.

How to query by month in mysql

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn