I need to get a list of the years with posts of given category. Like this: 2008, 2009, 2010, 2012, 2013 (if there were no posts in 2011, it's not included).

I try this query, and it works — I get my list of the years:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' GROUP BY YEAR(post_date) DESC");

Then I introduce category ID, and nothing works:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' AND cat = '4' GROUP BY YEAR(post_date) DESC");

And even simple query with category doesn't work:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} WHERE 
cat = '2' GROUP BY YEAR(post_date) DESC");

Category with this ID definitely exists — this is the part of url from the list of categories: term.php?taxonomy=category&tag_ID=2&post_type=post

There are more than a hundred posts it it.

I need to get a list of the years with posts of given category. Like this: 2008, 2009, 2010, 2012, 2013 (if there were no posts in 2011, it's not included).

I try this query, and it works — I get my list of the years:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' GROUP BY YEAR(post_date) DESC");

Then I introduce category ID, and nothing works:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} 
WHERE post_status = 'publish' AND cat = '4' GROUP BY YEAR(post_date) DESC");

And even simple query with category doesn't work:

$wpdb->get_results("SELECT YEAR(post_date) FROM {$wpdb->posts} WHERE 
cat = '2' GROUP BY YEAR(post_date) DESC");

Category with this ID definitely exists — this is the part of url from the list of categories: term.php?taxonomy=category&tag_ID=2&post_type=post

There are more than a hundred posts it it.

• wp-query
• categories
• query
• archives
• sql

3 Answers 3

Relationship between post and category is not stored in the posts table. You must extend your query for additional tables.

By category id:

SELECT YEAR(p.post_date) FROM {$wpdb->posts} p 
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id 
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id 
WHERE tt.term_id = 4 AND p.post_status = 'publish' AND p.post_type = 'post'
GROUP BY YEAR(p.post_date) DESC

Or by category slug:

SELECT YEAR(p.post_date) FROM {$wpdb->posts} p 
JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.id 
JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN {$wpdb->terms} t ON t.term_id = tt.term_id  
WHERE t.slug = 'your_category_slug' AND p.post_type = 'post' AND p.post_status = 'publish' 
GROUP BY YEAR(p.post_date) DESC

This is because the category-relations are not stored within the wp_posts table, but in another table named wp_term_relationships.

You have to do some joins to get what you want. This should give you an array of Years with the published posts in this year.

$years = $wpdb->get_results("SELECT DISTINCT YEAR($wpdb->posts.post_date) as year, COUNT($wpdb->posts.ID) as count FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->term_taxonomy.term_id = 4 AND $wpdb->posts.post_status = 'publish' AND
$wpdb->posts.post_type = 'post' 
GROUP BY YEAR($wpdb->posts.post_date) 
ORDER BY $wpdb->posts.post_date ASC");

Happy Coding!

There category is not stored in the wp_posts but in the wp_terms-table. Please have a look here. There query should probably be more like this one:

SELECT YEAR(post_date) FROM wp_posts WHERE post_status = "publish" AND ID IN (
    SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (
        SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = "category" AND term_id = 4
    )
) GROUP BY YEAR(post_date) DESC;

You could use also WP_Query if you need a list the of posts of a category of a specific year:

$params = [ 
    'cat' => 4, 
    'date_query' => [
        [ 'year' => 2016 ],
    ],
    'posts_per_page' => -1,
];
$query = new WP_Query( $params );