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

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.

Answer 2

Some options:

1. Save it as an integer representing the total number of seconds. "10:35" => 635
2. Save it as a timestamp object with no date component. "10:35" => MAKETIME(0, 10, 34)
3. Save it with leading decimals or spaces. "2:25" => " 2:25"

My preference would be for the first option.

Answer 3

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 4

You could try to see if

ORDER BY TIME_TO_SEC(timefield)

would parse it correctly, however it is not an optimal approach to store time as strings in the database, and I suggest that you store them as TIME if you are able to. Then you can use standard formatting functions to present them as you like.