I'm trying to select and group by date from MySQL.
table looks like this:
`id` int(11) NOT NULL,
`run_data` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`date_day` varchar(20) NOT NULL,
`number_of_links` int(20) NOT NULL,
SELECT date_day, SUM(number_of_links) FROM my_table GROUP BY date_day DESC LIMIT 30
$date_day = date('d/m/Y', time());
GROUP BY TO_DAYS(`run_data`)
GROUP BY MONTH(run_data)
GROUP BY DATE(STR_TO_DATE(run_data));
date_day field is not sortable. At least not unless you write custom logic for it, which is usually done in PHP; Or doing casting it on each query, which can be very expensive for the database (not to mention give incorrect results).
As mentioned in the comment by @nospor, the correct answer to this question is to alter the field to be a proper
date field. Then you can easily sort and/or group it.
To accomplish this I recommend looking up on the
ALTER TABLE syntax in the MySQL manual.