Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Isaac (WMF)
.
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. Data details: https://www.mediawiki.org/wiki/Manual:Image_table
Toggle Highlighting
SQL
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, img_name, img_metadata FROM image WHERE img_media_type = 'VIDEO' AND img_minor_mime = 'webm' LIMIT 10 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, img_name, img_metadata FROM image WHERE img_media_type = 'VIDEO' AND img_minor_mime = 'ogg' LIMIT 10 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, img_name, img_metadata FROM image WHERE img_media_type = 'VIDEO' AND img_minor_mime = 'mpeg' LIMIT 10
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...
