Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql - Group by month with Y-m-d format

Tags:

mysql

If I have column 'date' in format 'Y-m-d', can I use GROUP BY based on date column but group by months?

like image 988
stix Avatar asked Jan 19 '13 22:01

stix


People also ask

Can we group by month in SQL?

To group data by month in SQL Server, use the DATEPART() function. It extracts the given part (year, month, etc.) from the date. We use the function twice: once with the MONTH argument and once with the YEAR argument.

How can get date in dd mm yyyy format in MySQL?

The following is the output. The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.

How do I convert a date to month in MySQL?

Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column. (In our example, we use the start_date column of date data type).

How are Datetimes stored in MySQL?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.


1 Answers

Use MySQL's DATE_FORMAT() function to group only on the desired parts:

GROUP BY DATE_FORMAT(my_column, '%Y-%m')
like image 176
eggyal Avatar answered Sep 22 '22 22:09

eggyal