Untitled query #57961

SQL

SELECT block.log_title AS 'account blocked',
       TIMEDIFF(unblock.log_timestamp, block.log_timestamp) AS 'time to unblock',
       block.log_timestamp AS 'block timestamp', blocker.actor_name AS 'blocker', blockcom.comment_text AS 'block comment',
       unblock.log_timestamp AS 'unblock timestamp', unblocker.actor_name AS 'unblocker', unblockcom.comment_text AS 'unblock comment'
FROM logging AS unblock
LEFT JOIN actor_logging AS unblocker ON unblocker.actor_id = unblock.log_actor
LEFT JOIN comment_logging AS unblockcom ON unblockcom.comment_id = unblock.log_comment_id
JOIN logging AS block
  ON block.log_type = 'block'
 AND block.log_action = 'block'
 AND block.log_namespace = unblock.log_namespace
 AND block.log_title = unblock.log_title
 AND block.log_timestamp <= unblock.log_timestamp
 AND block.log_timestamp >= '20201230'
LEFT JOIN actor_logging AS blocker ON blocker.actor_id = block.log_actor
LEFT JOIN comment_logging AS blockcom ON blockcom.comment_id = block.log_comment_id
WHERE unblock.log_type = 'block' AND unblock.log_action = 'unblock'
  -- extremely crude regex to match an ipv4 or ipv6 address
  AND unblock.log_title NOT RLIKE '^([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+|[0-9A-Fa-f:]+)(/[0-9]+)?$'
  AND unblock.log_timestamp >= '2021'
  AND block.log_timestamp >= DATE_FORMAT(DATE_ADD(unblock.log_timestamp, INTERVAL -1 DAY), "%Y%m%d%H%i%s")
  -- previous time comparison is accurate, but unindexed; this is indexed, but inaccurate 
  AND unblock.log_timestamp - block.log_timestamp <= (20210301000000 - 20210228000000) -- greatest possible value for "1 day"
ORDER BY block.log_title ASC, unblock.log_timestamp ASC, block.log_timestamp ASC;
SQL

Checking query status...
