Custom post type yearly/ monthly archive permalinks

Question

I have a custom post type "news" in my Wordpress site. I am using Advanced Custom Fields plugin to add meta data to each post.

I want to create an array of news items as an archive:

[2013]
    [January] => 5
[2012]
    [January] => 20
    [February] => 10
[2011]
    [April] => 30

I managed to get this working using:

    global $wpdb;
    $news = $wpdb->get_results(
        "SELECT wp_posts.post_date, COUNT(wp_posts.ID) as count
         FROM $wpdb->posts
         WHERE
         wp_posts.post_type = 'news' AND
         wp_posts.post_status = 'publish' AND
         wp_posts.post_date <= CURDATE() AND
         wp_posts.post_date >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
         GROUP BY YEAR(wp_posts.post_date), MONTH(wp_posts.post_date)
         ORDER BY wp_posts.post_date DESC", 
         ARRAY_A);

    $archive = array();
    foreach ($news as $post):
        $year = date('Y', strtotime($post['post_date']));      
        $month = date('m', strtotime($post['post_date']));     
        $month_name = date('F', strtotime($post['post_date']));
        $post['url'] = 'NOT SURE ABOUT URL';
        $archive[$year][$month_name] = $post;
    endforeach;

I need to be able to link to specific years and months using http://example.com/news/2012/ and http://example.com/news/2012/10/.

I tried using the plugin Custom Post Type Archives but when I goto /news/2012/ I get the standard news listing with no filter applied.

Here is my Custom Post Type Code

register_post_type('news', 
  array(    
    'label' => 'News',
    'description' => 'Latest News',
    'public' => true,
    'show_ui' => true,
    'show_in_menu' => true,
    'capability_type' => 'post',
    'hierarchical' => true,
    'rewrite' => array('slug' => '','with_front' => '1'),
    'query_var' => true,
    'has_archive' => true,
    'menu_position' => 120,
    'menu_icon' => /assets/img/icon-custom.png
  )
);

Answer 1

Here's an example using add_rewrite_rule to handle years and months for a custom post type where news is the slug. Visit the Settings > Permalinks page in admin to flush rewrite rules after this is added. You could also put this in a plugin and flush rewrite rules on plugin activation.

function wpa83797_news_rewrite_rules(){

    add_rewrite_rule(
        'news/([0-9]{4})/([0-9]{1,2})/?$',
        'index.php?post_type=news&year=$matches[1]&monthnum=$matches[2]',
        'top'
    );

    add_rewrite_rule(
        'news/([0-9]{4})/?$',
        'index.php?post_type=news&year=$matches[1]',
        'top'
    );

}
add_action( 'init', 'wpa83797_news_rewrite_rules' );