的MySQL 5
表名是日程表。
出发时间数据类型= varchar（20）
询问
select * from schedules;
输出：
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
| travel_date | departure_time | origin_id | destination_id | operator_id | status | available_seats |
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
| 2014-06-09 | 02:30 PM | 134 | 251 | 2 | Active | 44 |
| 2014-06-09 | 09:30 PM | 134 | 251 | 2 | Active | 14 |
| 2014-06-09 | 10:00 PM | 134 | 251 | 2 | Active | 24 |
| 2014-06-09 | 12:30 PM | 134 | 251 | 2 | Active | 23 |
| 2014-06-09 | 11:15 PM | 134 | 251 | 2 | Active | 27 |
| 2014-06-09 | 09:30 PM | 134 | 251 | 4 | Active | 24 |
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
SELECT * FROM schedules
WHERE (travel_date ='2014-06-09' and origin_id ='134'
and destination_id ='251' and operator_id not in (SELECT id FROM operators WHERE (status != 'Active')) and status ='Active' and available_seats > 0)
ORDER BY departure_time ASC;
输出：
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
| travel_date | departure_time | origin_id | destination_id | operator_id | status | available_seats |
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
| 2014-06-09 | 02:30 PM | 134 | 251 | 2 | Active | 44 |
| 2014-06-09 | 09:30 PM | 134 | 251 | 2 | Active | 14 |
| 2014-06-09 | 09:30 PM | 134 | 251 | 4 | Active | 24 |
| 2014-06-09 | 10:00 PM | 134 | 251 | 2 | Active | 24 |
| 2014-06-09 | 11:15 PM | 134 | 251 | 2 | Active | 27 |
| 2014-06-09 | 12:30 PM | 134 | 251 | 2 | Active | 23 |
+-------------+----------------+-----------+----------------+-------------+--------+-----------------+
在这里，如何通过detail_time asc使此表顺序
例如：department_time应该是这样的：-12:30 PM，02：30 PM，09:30 PM，09：30 PM，10：00 PM，11:15 PM
通过这样的语句更改您的订单，这将有所帮助
ORDER BY STR_TO_DATE(timeField,'%h.%i%p');
数据类型TIME用于存储时间数据类型-表示没有AM / PM。将数据以24小时格式存储在您的数据库中，并使用以下其中一种在PHP或MySQL中使用am / pm将其格式化为12小时格式：
PHP：
$date = new DateTime($mysql_column['time']);
$date->format('h:i:s a');
或者：
$date = date('h:i:s a', strtotime($mysql_column['time']));
or MySQL:
SELECT DATE_FORMAT（'％h：％i：％s％p'，时间）FROM表；
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句