MySQL: Order by time (MM:SS)?

Question

I'm currently storing various metadata about videos and one of those bits of data is the length of a video.

So if a video is 10 minutes 35 seconds long, it's saved as "10:35" in the database.

But what I'd like to do is retrieve a listing of videos by length (longest first, shortest last).

The problem I'm having is that if a video is "2:56", it's coming up as longest because the number 2 is more than the number 1 in.

So, how can I order data based on that length field so that "10:35" is recognized as being longer than "2:56" (as per my example)?

Answer 1

SELECT * FROM table ORDER BY str_to_date(meta_time,'%l:%i')

You can find the specific formatters on the MySQL Website.

For example:

%k -> Hour (0..23)

%l -> Hour (1..12)

Answer 2

The easiest choice is to store a integer (seconds) or a float (minutes) instead of a string. So 10:35 would be 635 in seconds or 10.583 in minutes. You can sort by these numerically very easily. And you can output them in the format you'd like with some simple math and string functions.