Lengths of audio files powering Wikiradio stations

Note that any files whose lengths could not be determined by Commons on upload are excluded from the overall lengths printed.

SQL

select r.page_title, sum(if((p.pl_title like '%.oga') or (p.pl_title like '%.ogg') or (p.pl_title like '%.opus') or (p.pl_title like '%.OGG'),
     substring_index(substring_index(substring_index(q.img_metadata,'"length";',-1),";",1),":",-1),
     substring_index(substring_index(substring_index(q.img_metadata,'"playtime_seconds";',-1),";",1),":",-1)
                           ))/3600 as total_length
from metawiki_p.pagelinks p
join commonswiki_p.image q
on p.pl_title = q.img_name
join metawiki_p.page r
on p.pl_from = r.page_id
where r.page_title like 'Wikiradio_(tool)/playlist/%'
group by r.page_title
order by total_length desc;

SQL

Checking query status...