MySQL: time order (MM: SS)?

Question

I currently store various metadata about the video, and one of these data bits is the length of the video.

So, if the video lasts 10 minutes 35 seconds, it is saved as “10:35” in the database.

But what I would like to do is get a list of videos by length (longest, shortest).

The problem I am facing is that if the video is "2:56", it becomes the longest because the number 2 is greater than the number 1.

So, how can I sort the data based on this length field to recognize “10:35” as longer as “2:56” (as in my example)?

Answer 1

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

You can find specific formatting tools on the MySQL website.

For instance:

%k → Hour (0..23)

%l → Hour (1..12)

Answer 2

The easiest choice is to save an integer (seconds) or a float (minutes) instead of a string. Thus, 10:35 will be 635 seconds or 10.583 in minutes. You can easily sort them by quantity. And you can output them in the format you need using simple math and string functions.

Answer 3

Some options:

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

My preference would be for the first option.

Answer 4

You can try to see

ORDER BY TIME_TO_SEC (time field)

will analyze it correctly, however, this is not the optimal approach for storing time in the form of strings in the database, and I suggest that you store them as TIME if you are able to. You can then use the standard formatting functions to present them as you wish.

Answer 5

I had the same problem - saving the length of the video in the database.

I solved it using the TIME type mysql - it solves all problems with ordering and conversion.