luweiqi luweiqi - 28 days ago 8
PHP Question

Sort time in MySQL with multiple rows

Introduction



I have a table of buses in MySQL and each bus has a few arrival timings. I need to sort the buses by the first arrival.

Unsorted Table



+-------+-------------------------------+
| bus | time |
+-------+-------------------------------+
| Bus A | 8 a.m.<br/>9 a.m. |
| Bus B | 12 p.m.<br/>1 p.m.<br/>2 p.m. |
| Bus C | 10 a.m. |
| Bus D | 9:30 a.m. |
+-------+-------------------------------+


Sorted (Wrong)



+-------+-------------------------------+
| bus | time |
+-------+-------------------------------+
| Bus C | 10 a.m. |
| Bus B | 12 p.m.<br/>1 p.m.<br/>2 p.m. |
| Bus A | 8 a.m.<br/>9 a.m. |
| Bus D | 9:30 a.m. |
+-------+-------------------------------+


Sorted (Correct)



+-------+-------------------------------+
| bus | time |
+-------+-------------------------------+
| Bus A | 8 a.m.<br/>9 a.m. |
| Bus D | 9:30 a.m. |
| Bus C | 10 a.m. |
| Bus B | 12 p.m.<br/>1 p.m.<br/>2 p.m. |
+-------+-------------------------------+


Query



I've tried using this answer, but it doesn't seem to work. It might be due to formatting issues -- "2:00 AM" vs "2:00 a.m.".

SELECT bus_timing
FROM buses
ORDER BY STR_TO_DATE(bus_timing, '%l:%i %p')


Question



So, how can I sort these data according to the timings without changing the data at all?

Answer

The STR_TO_DATE format and approach looks good. So the data might be the problem.

This

ORDER BY STR_TO_DATE(REPLACE(UCASE(bus_timing),'.',''), '%l:%i %p')

will remove the dots in a.m. and p.m. and change them to upper case, which will be more in line with the expected %p format

Comments