MySQL Comparing Times of Different Formats

Question

I am working with a database full of songs, with titles and durations.

I need to return all songs with a duration greater than 29:59 (MM:SS).

The data is formatted in two different ways.

Format 1

Most of the data in the table is formatted as MM:SS, with some songs being greater than 60 minutes formatted for example as 72:15.

Format 2

Other songs in the table are formatted as HH:MM:SS, where the example given for Format 1 would instead be 01:12:15.

---

I have tried two different types of queries to solve this problem.

Query 1

The following query returns all of the values that I seek to return for Format 1, but I could not find a way to get values included for Format 2.

select title, duration from songs where 
  time(cast(duration as time)) > 
  time(cast('29:59' as time)) 

Query 2

With the next query, I hoped to use the format specifiers in str_to_date to locate those results with the format HH:MM:SS, but instead I received results such as 3:50. The interpreter is assuming that all of the data is of the form HH:MM, and I do not know how to tell it otherwise without ruining the results.

select title, duration from songs where
      time(cast(str_to_date(duration, '%H:%i:%s') as time)) >
      time(cast(str_to_date('00:29:59', '%H:%i:%s') as time))

I've tried changing the specifiers in the first call to str_to_date to %i:%s, which gives me all values greater than 29:59, but none greater than 59:59. This is worse than the original query. I've also tried 00:%i:%s and '00:' || duration, '%H:%i:%s'. These two in particular would ruin the results anyway, but I'm just fiddling at this point.

I'm thoroughly stumped, but I'm sure the solution is an easy one. Any help is appreciated.

EDIT: Here is some data requested from the comments below.

Results from show create table:

CREATE TABLE `songs` (
 `song_id` int(11) NOT NULL,
 `title` varchar(100) NOT NULL,
 `duration` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`song_id`),
 UNIQUE KEY `songs_uq` (`title`,`duration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Keep in mind, there are more columns than I described above, but I left some out for the sake of simplicity. I will also leave them out in the sample data.

Sample Data

title                       duration
(Allegro Moderato)              3:50
Agatha                          1:56
Antecessor Machine             06:16
Very Long Song              01:24:16
Also Very Long               2:35:22

Answer 1

After some research I have come up with an answer of my own that I am happy with.

select title, duration from songs where 
    case
    when length(duration) - length(replace(duration, ':', '')) = 1 
        then time_to_sec(duration) > time_to_sec('29:59')
    else time_to_sec(duration) > time_to_sec('00:29:59')
    end

Thank you to Gordon Linoff for suggesting that I convert the times to seconds. This made things much easier. I just found his solution a bit overcomplicated, and it reinvents the wheel by not using time_to_sec.

Output Data

title                     duration
21 Album Mix Tape            45:40
Act 1                      1:20:25
Act 2                      1:12:05
Agog Opus I                  30:00
Among The Vultures         2:11:00
Anabasis                   1:12:00
Avalanches Mixtape           60:00
Beautiful And Timeless       73:46
Beggars Banquet Tracks       76:07
Bonus Tracks                 68:55 
Chindogu                     66:23
Spun                        101:08

Note: Gordon mentioned his reason for not using time_to_sec was to account for songs greater than 23 hours long. After testing, I found that time_to_sec does support hours larger than 23, just as it supports minutes greater than 59.

It is also perfectly fine with other non-conforming formats such as 1:4:32 (e.g. 01:04:32).

Answer 2

You are storing unstructured data in a relational database. And that is making you unhappy. So structure it.

Either add a TIME column, or copy song_id into a parallel time table on the side that you can JOIN against. Select all the two-colon durations and trivially update TIME. Repeat, prepending '00:' to all the one-colon durations. Now you have parsed all rows, and can safely ignore the duration column.

Ok, fine, I suppose you could construct a VIEW that offers UNION ALL of those two queries, but that is slow and ugly, much better to fix the on-disk data.

Answer 3

Forget times. Convert to seconds. Here is one way:

select s.*
from (select s.*,
             ( substring_index(duration, ':', -1) + 0 +
               substring_index(substring_index(duration, ':', -2), ':', 1) * 60 +
               (case when duration like '%:%:%' then substring_index(duration, ':', 1) * 60*60
                     else 0
                end)
             ) as duration_seconds
      from songs s
     ) s
where duration_seconds > 29*60 + 59;