mysql group query by time

Mysql group query method by time: use the function [DATE_FORMAT (date, format)], date is the field name of the time type in the table, and format time output format.

Related learning recommendations: mysql database

Mysql group query method by time:

DATE_FORMAT(date,format): Function is used to display date/time data in different formats.

Parameters:

• #date: The name of the time type field in the table.

• format: time output format.

• format format:

• # #Statistics by year: SELECT DATE_FORMAT(date,'%Y') time,SUM(field name to be counted) FROM table name WHERE condition GROUP BY time

• Statistics by month: SELECT DATE_FORMAT(date,'%Y-%m') time,SUM(field name to be counted) FROM table name WHERE condition GROUP BY time

• Statistics by week: SELECT DATE_FORMAT(date ,'%Y-%m-%d') time,SUM (field name to be counted) FROM table name WHERE condition GROUP BY time

• Statistics by day: SELECT DATE_FORMAT(date ,'%Y-%m') time,SUM (field name to be counted) FROM table name WHERE condition GROUP BY time

• Statistics by week: SELECT DATE_FORMAT(date,'% Y-%u') time, SUM (field name that needs statistics) FROM table name WHERE condition GROUP BY time

Usage:

SELECT DATE_FORMAT(come_time,'%Y') year,count(*)  value FROM user  where registration_time >'2017'GROUP BY  year

Query results:

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