sql - MySQL Comparing Times of Different Formats -

i working database full of songs, titles , durations.

i need return songs duration greater 29:59 (mm:ss).

the data formatted in 2 different ways.

format 1

most of data in table formatted mm:ss, songs being greater 60 minutes formatted example 72:15.

format 2

other songs in table formatted hh:mm:ss, example given format 1 instead 01:12:15.

---

i have tried 2 different types of queries solve problem.

query 1

the following query returns of values seek return format 1, not find way values included format 2.

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

query 2

with next query, hoped use format specifiers in str_to_date locate results format hh:mm:ss, instead received results such 3:50. interpreter assuming of data of form hh:mm, , not know how tell otherwise without ruining results.

select title, duration songs       time(cast(str_to_date(duration, '%h:%i:%s') time)) >       time(cast(str_to_date('00:29:59', '%h:%i:%s') time)) 

i've tried changing specifiers in first call str_to_date %i:%s, gives me values greater 29:59, none greater 59:59. worse original query. i've tried 00:%i:%s , '00:' || duration, '%h:%i:%s'. these 2 in particular ruin results anyway, i'm fiddling @ point.

i'm thoroughly stumped, i'm sure solution easy one. appreciated.

edit: here data requested comments below.

results 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 more columns described above, left out sake of simplicity. leave them out in sample data.

sample data

title                       duration (allegro moderato)              3:50 agatha                          1:56 antecessor machine             06:16 long song              01:24:16 long               2:35:22 

you storing unstructured data in relational database. , making unhappy. structure it.

either add time column, or copy song_id parallel time table on side can join against. select two-colon durations , trivially update time. repeat, prepending '00:' one-colon durations. have parsed rows, , can safely ignore duration column.

ok, fine, suppose construct view offers union of 2 queries, slow , ugly, better fix on-disk data.