I am a beginner in MySQL and have registered a table in PhpMyAdmin , where id is int , and all other fields are text , including data_cadastro field. I was in doubt, because there were several types of fields with date name, and in a hurry, I kept text , because I was recording right.
But now I need to report the data, filtered by day, and the fields are formatted with dd/mm/aaaa hh:mm:ss . I need to do the SQL by doing a count of the data each day, and returning that data.
I'm searching, but I did not find the way to fit my case.
Count of filtered dates per day in MySQL
2
asked by anonymous 16.11.2015 / 18:53
3 answers
3
I was able to solve the problem by following the instructions in the answers / comments.
First I changed the field text to 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 just recovering the data in a loop, bringing the date and the count of daily records.
16.11.2015 / 21:44
3
You need to run a query to convert all of your fields with format dd/mm/aaaa hh:mm:ss to aaaa-mm-dd hh:mm:ss as this is the default used 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
datetimewithout it being properly formatted in the MySql pattern, the value will be lost.
16.11.2015 / 20:01
2
"because it was recording right". Did you use DATE?
The problem of not being recorded right (assuming you use DATE or another), it's likely that you entered data in the form other than "yyyy-mm-dd". Notice the features and the order. The format for TIME is hh: mm: ss.
The supported range is' 1000-01-01 'to' 9999 '. The supported range is' 1000-01-01' to '9999 -12-31 '. "
16.11.2015 / 22:55