SQL
x
set @list = "המקבץ השבועי/ענבים-2/דף משנה";
set @startdate = "2017-03-15";
set @summer = -3;
#------------------------------------------------
use hewiki_p;
set @newstartdate = date_add(concat(@startdate, " 00:00:00"), interval @summer hour);
set @newstopdate = date_add(@newstartdate, interval 8 day);
select distinct(concat('* [[{{subst:GENDER:', rev_user_text, '|משתמש|משתמשת}}:', rev_user_text, ']]')) from pagelinks join page
on page_title = pl_title
join revision
on rev_page = page_id
where pl_from in
(select page_id from page
where page_title = replace(@list, " ", "_")
and page_namespace = 4)
and page_namespace = 0
and rev_user > 0
and rev_parent_id = 0
and timediff(@newstopdate, rev_timestamp) > 0
and timediff(@newstartdate, rev_timestamp) < 0
order by rev_user_text;
select distinct(concat('* [[{{subst:GENDER:', rev_user_text, '|משתמש|משתמשת}}:', rev_user_text, ']]')) from pagelinks join page
on page_title = pl_title
join revision
on rev_page = page_id
where pl_from in
(select page_id from page
where page_title = replace(@list, " ", "_")
and page_namespace = 4)
and page_namespace = 0
and rev_user > 0
and not exists
(select * from user_groups
where rev_user = ug_user
and ug_group = "bot")
and timediff(@newstopdate, rev_timestamp) > 0
and timediff(@newstartdate, rev_timestamp) < 0
and not exists
(select * from revision
where rev_page = page_id
and rev_user > 0
and rev_parent_id = 0
and (substring(rev_timestamp, 1, 4) = "2016" and
((substring(rev_timestamp, 5, 5) = "04302" and
substring(rev_timestamp, 10, 1) in ("1", "2", "3")) or
(substring(rev_timestamp, 5, 3) = "050" and
substring(rev_timestamp, 8, 1) in ("1", "2", "3", "4", "5", "6")) or
(substring(rev_timestamp, 5, 4) = "0507" and
(substring(rev_timestamp, 9, 1) in ("0", "1") or
substring(rev_timestamp, 9, 2) = "20")))))
order by rev_user_text
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.