Get list of years when posts have been published

Question

I want to create a list of posts from a Custom Post Type and add some filters the user can click to filter the list.

I added filters for all the categories by using the get_terms() function. Now I want to add filters by year, so I need a way to retrieve a list of all the years in which at least one post has been published...

I just need something like:

$years = get_all_years_with_posts();
// returns array( 2011, 2013, 2014 )

I know I can grab all the posts, check their year, and build the list myself, but is not there any other approach?

Answer 1

Your question is pretty old, but I just wanted to add a real solution to your question. Here's a function that will return an array of years you have posts published in. You can put this function in functions.php or in a plugin or whatever you want.

function get_posts_years_array() {
    global $wpdb;
    $result = array();
    $years = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT YEAR(post_date) FROM {$wpdb->posts} WHERE post_status = 'publish' GROUP BY YEAR(post_date) DESC"
        ),
        ARRAY_N
    );
    if ( is_array( $years ) && count( $years ) > 0 ) {
        foreach ( $years as $year ) {
            $result[] = $year[0];
        }
    }
    return $result;
}

You'll need to modify it slightly for custom post types... just add AND wp_posts.post_type = 'my_cpt' somewhere in there.

Answer 2

You could use this function to get all the post years in a array, which you could use to loop with.

$terms_year = array(
    'post_type'         => array('YOUR_CPT'),
);

$years = array();
$query_year = new WP_Query( $terms_year );

if ( $query_year->have_posts() ) :
    while ( $query_year->have_posts() ) : $query_year->the_post();
        $year = get_the_date('Y');
        if(!in_array($year, $years)){
            $years[] = $year;
        }
    endwhile;
    wp_reset_postdata();
endif;

// Echo the years out wherever you want

echo '<pre>';
print_r($years);
echo '</pre>';

Answer 3

You could use a WP function and a shortcode this way :

add_shortcode( 'archives', '_get_archived_posts' );
function _get_archived_posts( $atts ) {

 $a = shortcode_atts( array(
    'type'            => 'yearly',
    'limit'           => '',
    'format'          => '', 
    'before'          => '',
    'after'           => '',
    'show_post_count' => false,
    'echo'            => 0,
    'order'           => 'DESC'
), $atts, 'filter_archives_sc' );

    return wp_get_archives($a);
}

Source

Answer 4

// Get all years posts publish;
function get_posts_years_array($post_type = 'post') {

    global $wpdb;
    $result = array();

    $query_prepare = $wpdb->prepare("SELECT YEAR(post_date) FROM ($wpdb->posts) WHERE post_status = 'publish' AND post_type = %s GROUP BY YEAR(post_date) DESC", $post_type);

    $years = $wpdb->get_results($query_prepare);

    if ( is_array( $years ) && count( $years ) > 0 ) {
        foreach ( $years as $year ) {
            $result[] = json_decode(json_encode($year), true);
        }
    }

    return $result;
}

Answer 5

I took the solution from @Gavin and enhanced it to add support for any post type and any post status:

/**
 * Get an array of available years for any post type with any post statuses
 * @see https://wordpress.stackexchange.com/a/273627/18713
 */
private function get_years_with_posts(
    string $post_type = 'post',
    array $post_stati = ['publish']
): array {
    /** @var wpdb $wpdb */
    global $wpdb;

    /** Convert the $post_stati array to the MySql IN equivalent */
    $post_status_in = "('" . implode("', '", array_map('esc_sql', $post_stati)) . "')";

    /** Prepare the query with the selected post type */
    $query = $wpdb->prepare(
        "SELECT YEAR(post_date)
        FROM {$wpdb->posts}
        WHERE post_status IN $post_status_in
        AND post_type = %s
        GROUP BY YEAR(post_date) DESC",
        $post_type
    );

    $years = $wpdb->get_results($query, ARRAY_N);

    return array_column($years, 0);
}

Usage example: Retrieve all published and future posts of the type event:

$avaliable_years = get_years_with_posts('event', ['publish', 'future']);