MySQL按时间顺序存储为HH：MM：SS

Question

I have a varchar typed field which stores strings in this format HH:MM:SS i.e. 01:25:59 (and sometimes without HH part e.g. 25:59).

I want to have a descending order of results based on this time and for that I came with [str_to_date()][1] function and currently I'm using str_to_date($field_value,'%l:%i:%s') DESC to achieve this kind of sorting.

The odd thing is by using this format %l:%i:%s all posts having this field in MM:SS format are ordered correctly but those in HH:MM:SS aren't.

1-So if I have these values:

11:35
15:20
48:00
01:57:47
01:20:26

2-They are sorted as:

48:00
01:20:26
15:20
11:35
01:57:47

3-Which is wrong and should be:

01:57:47
01:20:26
48:00
15:20
11:35

As you see in (2) only times in format of HH:MM:SS are not placed correctly (DESC)

How can I have the right sorting?

Answer 1

What about this?

SELECT  * FROM tbl 
ORDER BY TIME_TO_SEC(IF(LENGTH(str_time)<6,CONCAT("00:",str_time),str_time)) DESC

fiddle demo: http://sqlfiddle.com/#!9/4b5da/3