Total length of Commons video

There are two types of img_metadata data formats: JSONs and PHP-serialized strings. This approach handles both.
The metadata keys/values depend on the media type (ogg, mpeg, or webm). This approach handles all three.

Video files longer than 1 day in time are excluded as outliers / likely bad metadata.

Data details: https://www.mediawiki.org/wiki/Manual:Image_table
Additional statistics: https://commons.wikimedia.org/wiki/Special:MediaStatistics

SQL

WITH video_length AS (
  SELECT
    img_minor_mime,
    IF(img_minor_mime = 'ogg',
       ROUND(COALESCE(JSON_EXTRACT(img_metadata, '$.data.length'), SUBSTR(REGEXP_SUBSTR(img_metadata, '(s:6:"length";d:)[0-9]*\.?[0-9]*'), 16), 0), 3),
       ROUND(COALESCE(JSON_EXTRACT(img_metadata, '$.data.playtime_seconds'), SUBSTR(REGEXP_SUBSTR(img_metadata, '(s:16:"playtime_seconds";d:)[0-9]*\.?[0-9]*'), 27), 0), 3)
      ) AS video_length_seconds
 FROM image
 WHERE
   img_media_type = 'VIDEO'
)
SELECT
  img_minor_mime,
  COUNT(1) AS num_files,
  SUM(video_length_seconds) AS total_video_length_seconds
 FROM video_length
 WHERE
   video_length_seconds < (60 * 60 * 24)
 GROUP BY
   img_minor_mime

SQL

Checking query status...