Commons upload list

SQL

SELECT
    CONCAT('File:', page_title) AS Dateiname,
    img_timestamp,
    CASE
        WHEN img_metadata LIKE '%"DateTimeOriginal":%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, '"DateTimeOriginal":"', -1), '"', 1)
        WHEN img_metadata LIKE '%"DateTimeOriginal";s:10:"%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, 's:16:"DateTimeOriginal";s:10:"', -1), '"', 1)
        WHEN img_metadata LIKE '%"DateTimeOriginal";s:19:"%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, 's:16:"DateTimeOriginal";s:19:"', -1), '"', 1)
        WHEN img_metadata LIKE '%"DateTime";s:19:"%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(img_metadata, 's:8:"DateTime";s:19:"', -1), '"', 1)
        WHEN img_metadata REGEXP '([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
INNER JOIN categorylinks ON page_id = cl_from
INNER JOIN image ON page_title = img_name
WHERE
    img_metadata != ''
    AND img_timestamp BETWEEN '20000101' AND '20051231'
    AND cl_to LIKE '%Ahlbeck%'
    AND page_id IN (
        -- Hier kommt die Unterabfrage
        SELECT page_id
        FROM page
        INNER JOIN categorylinks ON page_id = cl_from
        INNER JOIN image ON page_title = img_name
        WHERE img_metadata != ''
        AND img_timestamp BETWEEN '20000101' AND '20051231'
        AND cl_to LIKE '%Ahlbeck%'
    )
GROUP BY
    Dateiname, img_timestamp, Zeit, img_metadata;



#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;
SQL

Checking query status...
