I am trying to display the number of records for the current month for a specific user. Unfortunately, it does not work, because At one time, the date format in the Database was not correctly indicated.
The text column, the date is written in plain text format.
How to be? I tried a lot, nothing comes out.
I try STR_TO_DATE, convert and other – the output is either null or mysql error.
I also tried to manually enter a request into phpmyadmin. Unsuccessfully.
Mysql version: 5.6.39-83.1 (Linux)
$modercount = mysqli_query($link, 'SELECT moder, COUNT(*) FROM my_logs WHERE server="'.$profileid.'" AND MONTH(time) = MONTH(NOW()) AND YEAR(time) = YEAR(NOW()) GROUP BY moder ORDER BY COUNT(*) DESC') or die(mysqli_error($link));
Date format in my sql column: 10.04.2018 12:52:18
3
Answers
This works for me.
Big thanks to @scaisedge and @o-jones
you should use str_to_date this way
First of all, get your textually formatted dates translated correctly to DATETIMEs. STR_TO_DATE() uses the same format strings as its reverse, DATE_FORMAT().
Second, select the range of datestamps you need like this, using LAST_DAY() to handle month arithmetic.
When you get a chance, add a new column to your table with the datestamps in DATETIME or TIMESTAMP format. That way your date-range selections can exploit indexes. How to do that is beyond the scope of your question today.