Simulating SELECT YEAR(post_date) SQL query using only WP Query

I’m not entirely sure this is possible but I’m completely out of ideas. The task I’m working on requires that I specifically use WP_Query due to constraints of other efficiency plugins my organization uses.

Pure and simple, I need a way to simulate the following query using WP_Query:

"SELECT YEAR(post_date) FROM wp_posts WHERE post_status = 'publish' GROUP BY YEAR(post_date) DESC"

Before you answer, note again, using the $wpdb global is off the table, and I’ve attempted to employ the parse_query method from WP_Query but it doesn’t seem to respond to the above query in any meaningful way.

Thank you to anyone who takes the time to think this over!

Answer

Probably try this:

<?php
//Alter the group by clause
function query_group_by_year_post_date_filter($groupby){
   return 'YEAR(wp_posts.post_date) DESC '; //try only post_date if this doesn't work
}
//Alter the select query
function query_select_fields($fields) {
  return 'YEAR(wp_posts.post_date)'; //try only post_date if this doesn't work
}

add_filter('posts_fields','query_select_fields');
add_filter('posts_groupby', 'query_group_by_year_post_date_filter'); 
$allPosts = get_posts(array( //we have to use get_posts instead of WP_Query cause get_posts supports supress_filters
    'post_type' => 'post', //specify post_type here or leave it in your case.
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'suppress_filters' => true //this seems to be preset in only get_posts and not WP_Query
)); 
remove_filter('posts_groupby', 'query_group_by_filter');
remove_filter('posts_fields','query_select_fields');
?>

I’ve tried not to use $wpdb in 2 functions at the top as per your requirements.

let me know if this worked for you.