SQL
x
USE commonswiki_p;
select distinct concat("File:", page_title) as Dateiname,
img_timestamp,
CASE WHEN REGEXP_SUBSTR(img_metadata,'("DateTimeOriginal":)')!=""
THEN SUBSTR(REGEXP_SUBSTR(img_metadata,'("DateTimeOriginal":(.*?[^[\\\\])")'), 21, 19)
WHEN REGEXP_SUBSTR(img_metadata,'(s:16:"DateTimeOriginal";s:10:")')!=""
THEN SUBSTR(REGEXP_SUBSTR(img_metadata,'(s:16:"DateTimeOriginal";s:10:"(.*?[^[\\\\])")'), 31, 10)
WHEN REGEXP_SUBSTR(img_metadata,'(s:16:"DateTimeOriginal";s:19:")')!=""
THEN SUBSTR(REGEXP_SUBSTR(img_metadata,'(s:16:"DateTimeOriginal";s:19:"(.*?[^[\\\\])")'), 31, 19)
WHEN REGEXP_SUBSTR(img_metadata,'(s:8:"DateTime";s:19:")')!=""
THEN SUBSTR(REGEXP_SUBSTR(img_metadata,'(s:8:"DateTime";s:19:"(.*?[^[\\\\])")'), 22, 19)
WHEN REGEXP_SUBSTR(img_metadata,'([1-9]|[12][0-9]|3[01])[-/.]([1-9]|1[012])[-/.]((19|20|)[0-9][0-9])')!=""
THEN REGEXP_SUBSTR(img_metadata,'([1-9]|[12][0-9]|3[01])[-/.]([1-9]|1[012])[-/.]((19|20|)[0-9][0-9])')
ELSE ""
END as Zeit,
img_metadata
from page
left join categorylinks
on page_id = cl_from
left join image
on page_title = img_name
where img_metadata != ""
and img_timestamp between "20070101" and "20071231"
and cl_to like "%Ahlbeck%";
#limit 150;
#SELECT page_title, rev_timestamp, rev_len
#FROM page
#JOIN revision ON page_id = rev_page
#WHERE page_namespace = 6
#AND rev_parent_id = 0
#AND rev_timestamp >= '20000101000000'
#AND rev_timestamp <= '20001231235959'
#AND rev_deleted = 0
#ORDER BY rev_timestamp DESC
#LIMIT 1000;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.