Meta Query Based on Month Range

Question

I'd like to sort events into 4 lists based on seasons (spring, summer, fall, winter).

If Spring is March 20th through June 20th, how can I loop through posts where the custom meta field (which defaults to mm/dd/yy format) falls within that date range while not being concerned by year?

Here's where I left off. $event_date_query prints the correct date format within the loop, but as long as I leave the current meta query, I get no posts found.

$spring_start = '2017-03-20';
$spring_end = '2017-06-20';
$event_date_query = date('Y-m-d', strtotime(get_post_meta($post->ID, '_wccf_pp_event_date', true)));

$args = array(
'post_type' => 'product',
'posts_per_page' => -1,
'meta_query' => array(
      array(
          'key' => $event_date_query, 
          'value' => array($spring_start, $spring_end),
          'compare' => 'BETWEEN', 
          'type' => 'DATE',
      ),
  ),
);

Answer 1

The problem is that you're passing a meta VALUE in the key field of the meta_query...you should be passing your meta KEY there ("_wccf_pp_event_date"), as in:

$seasons = array (
    'spring' => array ('2017-03-21', '2017-06-20'),
    'summer' => array ('2017-06-21', '2017-09-20'),
    'fall' => array ('2017-09-21', '2017-12-20'),
    'winter' => array ('2017-12-21', '2018-03-20'),
    ) ;

foreach ($seasons as $season => $date_range) {
    $args = array (
        'post_type' => 'product',
        'posts_per_page' => -1,
        'meta_query' => array (
            array (
                // the 'key' should be the meta field, NOT a value you're looking for
                // in that field
                'key' => '_wccf_pp_event_date', //$event_date_query, 
                'value' => $date_range,
                'compare' => 'BETWEEN', 
                'type' => 'DATE',
                ),
            )
        ) ;
    $query = new WP_Query ($args) ;

    if ($query->has_posts ()) {
        while ($query->has_posts ()) {
            $query->the_post () ;
            // do something with this post
            }
        }
    else {
        echo "no $season products" ;
        }
    }

Edit, to answer question asked in comment

Off the top of my head, : not via a standard WP_Query date_query.

There might be a way to hook into the SQL produced from a meta_query to incorpoarate one/more of the MySQL Date and Time Functions (I'd start by looking at get_meta_sql), but I doubt those date & time funcs even would even do it.

However, there's always the "brute force" way:

1. find the min/max years for the dates in your post_meta
2. iterate from min to max, performing the meta_query from my original answer
3. merge the posts found in each iteration

That would look something like:

$sql = "
    SELECT YEAR(MIN(meta_value)), YEAR(MAX(meta_value))
    FROM $wpdb->postmeta
    WHERE meta_key = '_wccf_pp_event_date'
    " ;
list ($min_year, $max_year) = array_map ('intval', $wpdb->get_row ($sql, ARRAY_N)) ;

for ($year = $min_year ; $year < $max_year ; $year++) {
    // insert code from my 1st answer here, substituting $year into the dates in the $seasons array
    // (remembering to increment $year by 1 when substituting into the end date for winter)
    }

There is also the complication that the dates of the equinoxes/solstices differ from year to year (i.e., spring starts on March 20 in some years, March 19, in others, etc). However, unless your "products" are "astronomical" in nature, you can probably ignore that complication and just use the same month/day values for any year.