Filter post listing by meta value which is a date

This question is almost what Im trying to achieve. Create a filter for posts based on a custom field.

My custom field is a date and I would like to create a date filter (monthly) based on this custom field for the post listing admin screen.

I’m not using the normal date as this is an historical website I’m creating and some posts are from many years ago.

Add a query variable to store the month

So first of all it’ll be necessary to create a custom query variable – this will store the month we’re after (in 2012/6) format. I’ll call it custom_month, but its best practise to prefix it to avoid clashes with other plug-ins:

add_filter('query_vars', 'wpse57344_register_query_vars' );
function wpse57344_register_query_vars( $qvars ){
    //Add these query variables
    $qvars[] = 'custom_month';
    return $qvars;
}

Add the drop-down

The query variable will now be recognised and any given value stored by WordPress in the main query (global $wp_query).

Next we create the drop-down to go at the top of the post admin table (this can be changed to be any post type).

add_action( 'restrict_manage_posts', 'wpse57344_restrict_posts_by_metavalue' );
function wpse57344_restrict_posts_by_metavalue() {
    global $typenow;
    $months = wpse57344_get_months();
    if ($typenow == 'post') {
        $selected = get_query_var('custom_month');
        $output = "<select style="width:150px" name="custom_month" class="postform">\n";
        $output .= '<option '.selected($selected,0,false).' value="">'.__('Show All','wpse57344_plugin').'</option>';
        if ( ! empty( $months ) ) {
            foreach ($months as $month):
                $value =esc_attr($month->year."https://wordpress.stackexchange.com/".$month->month);
                $month_dt = new DateTime($month->year.'-'.$month->month.'-01');
                $output .= "<option value="{$value}" ".selected($selected,$value,false).'>'.$month_dt->format('F Y').'</option>';
            endforeach; 
        }
        $output .= "</select>\n";       
    echo $output;
    }
}

The above gets an array of months a month object consists of:

year => //the year of the month e.g. 2012
month => //the number of the month, e.g. 6 for July
posts => //number of posts with the date meta value in this month

Obviously wpse57344_get_months() doesn’t exist natively – we’ll construct it later.

Assuming with an array of months, we create the drop-down with each option having the value of the form yyyy/mm. I’ve given the form the same name as the query variable we added.

Altering the query

When a month in the drop-down is selected, the month in yyyy/mm is posted as the value for custom_month. Because we’ve registered this variable name with WordPress we can access it through $query->get('custom_month').

So we check if its empty or not – if its not, then we restrict to posts where their meta value data is in that month. To do that we use a meta query and the BETWEEN operator.

add_action( 'pre_get_posts', 'wpse57351_pre_get_posts' );
function wpse57351_pre_get_posts( $query ) {

    //Only alter query if custom variable is set.
    $month_str = $query->get('custom_month');
    if( !empty($month_str) ){

            //For debugging, uncomment following line
            //var_dump($query);

        //Be careful not override any existing meta queries.
        $meta_query = $query->get('meta_query');
        if( empty($meta_query) )
            $meta_query = array();

        //Convert 2012/05 into a datetime object get the first and last days of that month in yyyy/mm/dd format
        $month = new DateTime($month_str.'/01');

        //Get posts with date between the first and last of given month
        $meta_query[] = array(
            'key' => 'customdate',
            'value' => array($month->format('Y/m/d'),$month->format('Y/m/t')),
            'compare' => 'BETWEEN',
        );
        $query->set('meta_query',$meta_query);

            //For debugging, uncomment following line
            //var_dump($query);
    }
}

Get the months

It remains then to define th function wpse57344_get_months(). We need to query the postmeta table and pick out distinct months from the dates in your posts’ meta. In the following I assume that your date is stored with key ‘customdate’ and has format (as indicated in comments) yyyy/mm/dd.

function wpse57344_get_months(){
    global $wpdb;
        $months = wp_cache_get( 'wpse57344_months' );
        if ( false === $months ) {
            $query = "SELECT YEAR(meta_value) AS `year`, MONTH(meta_value) AS `month`, count(post_id) as posts 
                FROM $wpdb->postmeta WHERE meta_key ='customdate'           
                GROUP BY YEAR(meta_value), MONTH(meta_value) ORDER BY meta_value DESC";
            $months = $wpdb->get_results($query);
            wp_cache_set( 'wpse57344_months', $months );
        }
        return $months;
}

This returns an array of month objects in the desired format.