new articles in mediawiki

In mediawiki, we can have stats about the total of articles and revisions throught the special page Special:Statistics. You can even have a look at the revision table, but there is no field about when an article was created (though you can always have a look at the history of each page), and thus no direct way to know how many of them were created each week or month.

With this sql code you can get the list of new articles in mediawiki for up to 2010:

SELECT p.page_title, min( r.rev_timestamp )
FROM revision r, page p
WHERE r.rev_page = p.page_id
GROUP BY p.page_title
HAVING MIN( r.rev_timestamp ) < '2010'

It is rather easy to get the list for a specific month, for example, February 2010, just change the last line with:

HAVING MIN( r.rev_timestamp ) < '201003' AND MIN( r.rev_timestamp ) > '201002' )

… which includes all dates starting with 201002 (yyyymm, year 4 char: 2010, month 2 char: 02, February), but smaller (before) 201003 (March 2010). VoilĂ .

The mediawiki database diagram for the tables implied is:

revisions page db mediawiki

share this on...
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
  • email
  • Google Buzz
  • LinkedIn
  • Meneame
  • PDF
  • RSS

Creative Commons License
The new articles in mediawiki by geometrus, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

This entry was posted in mediawiki, mysql, statistics and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>