MySQL sorting upon date and time on datetime string

Question

I have a MySQL table with the following schema:

CREATE TABLE `events` (
  `id` bigint(20) NOT NULL,
  `name` text NOT NULL,
  `time` text NOT NULL,
  `city` text NOT NULL,
  `description` text NOT NULL,
  `contact_name` text NOT NULL,
  `contact_number` text NOT NULL,
  `contact_email` text NOT NULL,
  `image` text NOT NULL,
  `main_event` int(11) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now we can see that time column is type of text. A sample entry from time is given below:

12/09/2017 05:45:00 pm
12/09/2017 02:00:00 pm
13/09/2017 10:30:00 am

Now what I want is to get entries separated by Day i.e. entries for 12/09/2017 and 13/09/2017, also they must be sorted by time in ascending order. So my sample output will be like below

12/09/2017 02:00:00 pm
12/09/2017 05:45:00 pm

then

13/09/2017 10:30:00 pm

I tried to get ID using group concat and tried to convert this text type date string using the combination of DATE_FORMAT and STR_TO_DATE. But the problem is that I am not able to sort it correctly. If I use %p argument with STR_TO_DATE the output becomes NULL.

I am currently using the following query

SELECT GROUP_CONCAT(id) id, DAY(DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y'), '%Y-%m-%d')) day, MONTHNAME(DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y'), '%Y-%m-%d')) month, DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y'), '%Y-%m-%d') test FROM events WHERE main_event >= 0 GROUP BY DAY(DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y'), '%Y-%m-%d')), MONTH(DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y'), '%Y-%m-%d')) ORDER BY test ASC

And the output is:

id          day   month      test
13,10,14    11    September  2017-09-11
15,16,19    12    September  2017-09-12

But what I want is that id should also be sorted based on hour, i.e. 9:00:00 am entry will come first, then 10:00:00 am entry.

Also on a second thought, I tried to sort them after getting the result like below:

SELECT DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y %h:%i %p'), '%d/%m/%Y %h:%i %p') t FROM events WHERE id = 15 or id = 16 or id = 19 ORDER BY t ASC

But the result is NULL

Answer 1

I found my error myself. Actually, I missed parsing seconds(%s) given in the time value.

DATE_FORMAT(STR_TO_DATE(time, '%d/%m/%Y %h:%i:%s %p'), '%H:%i') t