This query is marked as a draft This query has been published by Erell.

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.

Checking query status...