I am trying to add a filter on my custom theme based on post month, similar with the archives but with some differences.

What is the best way to get a list of months in witch we have posts?

Thanks, Alex

I used the following query:

 $wpdb->get_results("SELECT DISTINCT YEAR(post_date) AS `year`, MONTH(post_date) AS   `month`, count(ID) as posts 
                     FROM $wpdb->posts 
                     WHERE post_type = 'post' AND post_status = 'publish' 
                     GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC"

Could not use the wp_get_archives function because it returns only the following formats:

html - In HTML list (<li>) tags and before and after strings. This is the default.
option - In select (<select>) or dropdown option (<option>) tags.
link - Within link (<link>) tags.
custom - Custom list using the before and after strings.
  • If you can include some more details (such as code you're using or have tried, the specifics of what you're trying to do, and something that haven't worked), I suspect you'll get a much better answer. As it stands, it's hard to give you an specific-enough answer because it's not quite clear what you want.
    – mrwweb
    May 17, 2012 at 6:17
  • I ended using the query mentioned above, not quite sure if a better way exists
    – thedev
    May 17, 2012 at 14:46

3 Answers 3


Core can't help you here. You'll have to do a…

Custom Query

Here's a save query, that I use on admin UI screens to get the total amounts of month with posts to use them in the custom pagination. Pay attention that I query not only for the published posts, but take into consideration that there might be some restriction set and apply it then.

$post_status = esc_attr( $_GET['post_status'] );
$post_status = in_array( $post_status, $GLOBALS['avail_post_stati'] )
    ? " AND post_status = {$post_status}"
    : 'all'
'all' === $post_status AND $post_status = '';

$total_page_dates = $wpdb->get_results( $wpdb->prepare( "
        YEAR( post_date )  AS year,
        MONTH( post_date ) AS month,
        count( ID )        AS posts
    FROM {$wpdb->posts}
        post_type = %s
        YEAR( post_date ),
        MONTH( post_date )
    ORDER BY post_date
", get_current_screen()->post_type, $post_status ) );


You'll then be able to inspect your result

// Inspect the result
var_dump( $total_page_dates );

Which might look like the following:

array (size=4)
  0 => 
      public 'year' => string '2013' (length=4)
      public 'month' => string '6' (length=1)
      public 'posts' => string '19' (length=2)
  1 => 
      public 'year' => string '2013' (length=4)
      public 'month' => string '7' (length=1)
      public 'posts' => string '17' (length=2)
  2 => 
      public 'year' => string '2013' (length=4)
      public 'month' => string '8' (length=1)
      public 'posts' => string '8' (length=1)
  3 => 
      public 'year' => string '2013' (length=4)
      public 'month' => string '9' (length=1)
      public 'posts' => string '2' (length=1)

You are then able to loop through it or simply grab the first or last array item to get the range. Counting - count( $total_page_dates ) - would tell you how many month you got, etc. Keep in mind that every array value is an object, so you have to access it like this

$posts_in_first_month = total_page_dates[0]->posts;

Try wp_get_archives()

Last Twelve Months

Displays archive list by month, displaying only the last twelve.

<?php wp_get_archives('type=monthly&limit=12'); ?>

Last Fifteen Days

Displays archive list by date, displaying only the last fifteen days.

<?php wp_get_archives('type=daily&limit=15'); ?>

Last Twenty Posts

Displays archive list of the last twenty most recent posts listed by post title.

<?php wp_get_archives('type=postbypost&limit=20&format=custom'); ?>

For a non-formatted solution, consider using query_posts()

    $allPosts = query_posts('cat=6&monthnum=04&year=2011');
  • wp_get_archives returns a formated result, I only need the month and year value (01.2005, 02.2005 ...)
    – thedev
    May 17, 2012 at 14:45
  • @thedev Updated my answer with an example using query_posts() May 17, 2012 at 17:43
  • 1
    Thanks for your update, however I am not interested in the posts :) I am interested in the months that have posts.
    – thedev
    May 17, 2012 at 17:45
  • Do NOT user query_posts(). Micheal, please revisit that answer. :)
    – kaiser
    Jun 21, 2013 at 13:21

This is the query that wp_get_archive is internally using for month archives

$query = "SELECT YEAR(post_date) AS `year`, MONTH(post_date) AS `month`, count(ID) as posts FROM $wpdb->posts $join $where GROUP BY YEAR(post_date), MONTH(post_date) ORDER BY post_date $order $limit";

and you are doing same thing, except that using distinct and group by together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.