Filtering posts between two dates, based on selected month, with a meta query

• MDesigns

• May 30, 2024 at 2:52 pm

I have some filters on my post archive and one of them is a month dropdown. The post type has 2 custom fields, one for start date and one for end date. I am trying to add a meta query to retrieve posts where the selected month falls between these two dates.

IE if a post has a start_date of 20240502 and the end_date is 20240815, for example, it would always show up if the user selected any month between May(05) and August(08).

I tried modifying a response from another post I found:

$sel_month = '05';
$meta_query[] = array(
    'relation' => 'OR',
    array(
        'key'      => 'start_date',
        'compare'  => 'REGEXP',
        'value'    => '[0-9]{4}' . $sel_month . '[0-9]{2}',
    ),
    array(
        'key'      => 'end_date',
        'compare'  => 'REGEXP',
        'value'    => '[0-9]{4}' . $sel_month . '[0-9]{2}',
    )
);

But with this, it will only show the post if the start_date month is selected or the end_date month is selected but not any of the months between.

Any ideas how I can modify this query to show all posts where the selected month falls between the start_date and end_date of the post?

• jenniwilli

• May 30, 2024 at 4:47 pm

You need to use a more complex meta query that checks if the selected month lies within the range of the start and end dates. Here’s an updated version of your query that should achieve this:

$sel_month = '05';
$sel_year = '2024'; // Adjust as needed or get this dynamically
$sel_month_start = $sel_year . $sel_month . '01';
$sel_month_end = date("Ymt", strtotime($sel_month_start));

$meta_query = array(
    'relation' => 'AND',
    array(
        'key'     => 'start_date',
        'value'   => $sel_month_end,
        'compare' => '<=',
        'type'    => 'DATE'
    ),
    array(
        'key'     => 'end_date',
        'value'   => $sel_month_start,
        'compare' => '>=',
        'type'    => 'DATE'
    ),
);

$query = new WP_Query(array(
    'post_type' => 'your_post_type',
    'meta_query' => $meta_query
));

This code constructs a date range for the selected month and uses it to make sure the post’s start date is before the end of the selected month and the post’s end date is after the start of the selected month. And plz remember to adjust 'your_post_type' to your actual post type.

• MDesigns

• May 30, 2024 at 5:51 pm

That worked perfectly, thank you so much!