Counting filtered dates per day in Mysql

Question

I’m a beginner in Mysql and registered a table on Phpmyadmin, where the id is int, and all other fields are text, including a field data_cadastro. I was in doubt, because there were several types of fields named date, and in haste, I kept text, 'cause I was recording right. But now I need to generate data report, filtered per day, and the fields are formatted dd/mm/aaaa hh:mm:ss. I need to do SQL, doing a daily data count, and returning this data. I’m researching, but I haven’t found the proper way to my case.

Answer 1

I was able to solve the problem by following the instructions given in the answers/ comments.

First I changed the field text for datetime and then with an SQL query I solved my problem.

Select DATE(data_cadastro),
count(data_cadastro) as TOTAL from CHECKIN_TAB
group by DATE(data_cadastro)

Then it was only to recover the data in a loop, bringing the date and the counting of daily records.

Answer 2

You need to run a query to convert all your formatted fields dd/mm/aaaa hh:mm:ss for aaaa-mm-dd hh:mm:ss because this is the standard adopted by mysql.

Query example

UPDATE tabela SET data_cadastro = CONCAT(SUBSTRING(data_cadastro, 7,4), "-", SUBSTRING(data_cadastro, 4,2), "-", SUBSTRING(data_cadastro, 1,2)," ", SUBSTRING(data_cadastro, 12) ) ;

After doing this convert the field type to datetime

Note: If you convert the field type to datetime without it being properly formatted in Mysql default the value will be lost.

Answer 3

"because it was recording right". You used DATE?

The problem of not being saved right (assuming the use of DATE or other), is probably that you entered data in the formed other than "yyyy-mm-dd". Note the strokes and the order. The format for TIME is hh:mm:ss.

"The DATE type is used for values with a date part but no time part. Mysql retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'."

https://dev.mysql.com/doc/refman/5.7/en/datetime.html