How to Group Data by Date from a DATETIME Field in MySQL?

Casting DATETIME as DATE in MySQL

Casting DATETIME as DATE is necessary when you want to group data by date in a database query, but the database only stores a DATETIME field. The DATE() function in MySQL allows you to achieve this by extracting only the date portion from a DATETIME column.

Original Query Error

The original query provided:

select * from follow_queue group by follow_date cast follow_date as date

fails to work because the cast follow_date as date syntax is incorrect.

Solution: Using the DATE() Function

To correctly cast DATETIME as DATE, use the DATE() function as follows:

select * from follow_queue group by DATE(follow_date)

The DATE() function accepts a DATETIME expression as its argument and returns only the date portion. By grouping the results by the DATE(follow_date) expression, you can effectively group the entries by date, even though the database stores the timestamp in a DATETIME format.

The above is the detailed content of How to Group Data by Date from a DATETIME Field in MySQL?. For more information, please follow other related articles on the PHP Chinese website!