mariusz mariusz - 2 months ago 6
MySQL Question

sql select and group by CURRENT_TIMESTAMP

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,


for selecting, I'm using:

SELECT date_day, SUM(number_of_links) FROM my_table GROUP BY date_day DESC LIMIT 30


I'm getting total number of links per day but I can not sort it by date. date_day from my database is just:

$date_day = date('d/m/Y', time());


any ideas how to fix that ?

what I tried so far:

GROUP BY TO_DAYS(`run_data`)
GROUP BY MONTH(run_data)
GROUP BY DATE(STR_TO_DATE(run_data));

Answer

Your 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.