php - MySQL根据日期时间字符串的日期和时间排序

Question

我有一个具有以下架构的 MySQL 表:

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;

现在我们可以看到时间列是文本类型。下面给出了时间的示例条目:

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

现在我想要的是获取按天分隔的条目，即 12/09/2017 和 13/09/2017 的条目，它们也必须按时间升序排序。所以我的示例输出将如下所示

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

然后

13/09/2017 10:30:00 pm

我尝试使用 group concat 获取 ID，并尝试使用 DATE_FORMAT 和 STR_TO_DATE 的组合转换此文本类型日期字符串。但问题是我无法正确排序。如果我将 %p 参数与 STR_TO_DATE 一起使用，则输出变为 NULL。

我目前正在使用以下查询

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

输出是:

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

但我想要的是 id 也应该根据小时排序，即 9:00:00 am 条目将排在第一位，然后是 10:00:00 am 条目。

再三考虑，我尝试在得到如下结果后对它们进行排序:

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

但结果为NULL

Answer 1

我自己发现了错误。实际上，我错过了时间值中给出的解析秒数(%s)。

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