Sort time in MySQL with multiple rows

Question

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?

why are there nulls in your bus column? This is not excel this is a database — e4c5
– e4c5, Commented Nov 7, 2016 at 10:51
your data is still wrong. The times shouldn't be in a single column separated by line breaks. In fact they shouldn't be in a single column at all — e4c5
– e4c5, Commented Nov 7, 2016 at 10:55

Thomas G · Accepted Answer · 2016-11-07 11:02:18Z

1

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

answered Nov 7, 2016 at 11:02

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Panda Over a year ago

Thanks, but it doesn't seem to work. Would it be possible to remove everything after the <br/> part?

Thomas G Over a year ago

Wait... you have those <br/> in the database?? I thought it was just a formatting issue while copy/pasting here

Thomas G Over a year ago

I also see that you edited your question and changed how the data is displayed. For the bus B, when I asnwred you had one row for each time, and now everything is grouped together. Very confusing

ScaisEdge · Accepted Answer · 2016-11-07 11:21:50Z

1

If you can format the date this way 8 00 am instead of 8 a.m. you could use

select STR_TO_DATE( substr('8 30 am<br/>9 a.m. ', 1, 
              LOCATE('<br/>','8 30 am<br/>9 a.m. ') -1), '%l%i %p');

You should obtain the time only for the first value and sort for this

edited Nov 7, 2016 at 11:21

answered Nov 7, 2016 at 11:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2016-11-07 11:52:24Z

1

Use substring_index() to break the string apart and get the first element. Then sort by the first element.

So:

order by str_to_date(substring_index(time, '<', 1), '%l:%i %p')

answered Nov 7, 2016 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Sort time in MySQL with multiple rows

Introduction

Unsorted Table

Sorted (Wrong)

Sorted (Correct)

Query

Question

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Introduction

Unsorted Table

Sorted (Wrong)

Sorted (Correct)

Query

Question

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related