使用meta_query进行确切的日期范围搜索

Question

I have a date range search tool on a wordpress website to help find events that happen within a particular time-frame. I am using ACF pro's repeater to list dates within an event post. The date range tool is correctly pulling up events where dates for the event occurs within the date range, but it is also including events which do not have dates listed within the date range but where the first and last date are either side of the the date range.

I need to find a solution that means only events show up in the date range search if they have an exacting date within range.

Here is the loop / meta_query I'm using:

<?php  // filter
function my_posts_where( $where ) {

$where = str_replace("meta_key = 'showings_%", "meta_key LIKE 'showings_%", $where);

return $where;
}

add_filter('posts_where', 'my_posts_where');

  $_sda = $_GET['sda'] != '' ? $_GET['sda'] : '';
  $_smo = $_GET['smo'] != '' ? $_GET['smo'] : '';
  $_syr = $_GET['syr'] != '' ? $_GET['syr'] : '';

  $startd = $_syr.$_smo.$_sda.'0000';

  $_eda = $_GET['eda'] != '' ? $_GET['eda'] : '';
  $_emo = $_GET['emo'] != '' ? $_GET['emo'] : '';
  $_eyr = $_GET['eyr'] != '' ? $_GET['eyr'] : '';

    $endd = $_eyr.$_emo.$_eda.'2359';

    $meta_query = array(
                        'posts_per_page'    => -1,
                        'post_type'     => 'events',
                        'meta_key'      => 'showings_%_show_when',
                        'orderby'   => 'meta_value',
                        'order' => 'ASC',
                        'meta_query' => array(
                            'relation' => 'BETWEEN',
                            array(
                                'key'       => 'showings_%_show_when',
                                'value'     => $startd,
                                'compare'   => '>=',
                                'type'      => 'NUMERIC'
                            ),
                            array(
                                'key'       => 'showings_%_show_when',
                                'value'     => $endd,
                                'compare'   => '<=',
                                'type'      => 'NUMERIC'
                            )
                        )
                    );

    // query
    $the_query = new WP_Query( $meta_query ); ?>

I hope this is enough information to go on. Here's a live example if it helps.

The events that are appearing incorrectly are LegaC and MyMoves - the events themselves do not occur during this time period.

Thanks very much,

Answer 1

This has been resolved with a bit of redrafting of the query. See here:

$meta_query = array(
                        'posts_per_page'    => -1,
                        'post_type'     => 'events',
                        'orderby'   => 'meta_value',
                        'order' => 'ASC',
                        'meta_query' => array(
                            'relation' => 'AND',
                            array(
                                'key' => 'showings_%_show_when',
                                'value' => array( $startd, $endd ),
                                'type' => 'NUMERICAL',
                                'compare' => 'BETWEEN'
                            )
                        )
                    );