hrwiki – nepatrolirane promjene po datumu

SQL

SET @from = "20231002";


SET @to   = DATE_FORMAT(@from + INTERVAL 4 DAY, "%Y%m%d");
SET @patrol_max   = DATE_FORMAT(@from + INTERVAL 32 DAY, "%Y%m%d"); /*patrolirati se može samo tridesetak dana od promjene*/


SELECT page_title, actor_name, rev_id, rev_timestamp, rev_actor FROM revision
JOIN actor ON actor_id = rev_actor 
JOIN page ON page_id = rev_page
LEFT JOIN user_groups ON ug_user = actor_user
WHERE 
    /*actor_user is NOT NULL AND */
    rev_timestamp BETWEEN @from AND @to
AND ug_group is null
AND rev_id NOT IN (SELECT SUBSTRING(log_params FROM 26 FOR 7) FROM logging WHERE log_timestamp>@from AND log_timestamp <= @patrol_max AND log_action="patrol")
;
SQL

Checking query status...
