WP_Query meta_query date range with 2 custom fields

Question

I have a custom post type 'events' that contains custom fields for start_date and end_date. I'm trying to make a simple list of upcoming events. If I query only events with a start_date >= today it works fine. If I add in a meta_query to say "AND" all events with an end_date <= today - it doesn't return anything.

My custom fields (start_date, end_date) are stored as unix timestamps, which is why I'm using 'NUMERIC' and 'meta_value_num'. $today is a timestamp of the current date. Here are examples of what I'm trying to do... Pulling my hair out - any help would be appreciated!

THIS WORKS:

$args = array(  
    'post_type' => 'events',
    'posts_per_page' => -1,
    'meta_key' => 'start_date',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
    'meta_query' => array(
        array(
            'key' => 'start_date',
            'value' => $today,
            'compare' => '>=',
            'type' => 'NUMERIC',
        ),

    )
);

When I add in a second array for the meta_query - DOES NOT WORK:

$args = array(  
    'post_type' => 'events',
    'posts_per_page' => -1,
    'meta_key' => 'start_date',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'start_date',
            'value' => $today,
            'compare' => '>=',
            'type' => 'NUMERIC',
        ),
        array(
            'key' => 'end_date',
            'value' => $today,
            'compare' => '<=',
            'type' => 'NUMERIC',
        ),

    )
);

When I use meta_compare in the main query, and a single meta_query - DOES NOT WORK:

$args = array(  
    'post_type' => 'events',
    'posts_per_page' => -1,
    'meta_key' => 'start_date',
    'meta_value_num' => $today,
    'meta_compare' => '>=',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'end_date',
            'value' => $today,
            'compare' => '<=',
            'type' => 'date',
        ),

    )
);

When I use meta_compare in the main query, and multiple meta_queries - DOES NOT WORK:

$args = array(  
    'post_type' => 'events',
    'posts_per_page' => -1,
    'meta_key' => 'start_date',
    'meta_value_num' => $today,
    'meta_compare' => '>=',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'start_date',
            'value' => $today,
            'compare' => '>=',
            'type' => 'NUMERIC',
        ),
        array(
            'key' => 'end_date',
            'value' => $today,
            'compare' => '<=',
            'type' => 'NUMERIC',
        ),

    )
);

Answer 1

This is how I'm using it and its working fine for me.

$the_query = new WP_Query(array(
    'post_type' => 'job',
    'posts_per_page'   => 25,
    'meta_query' => array(
        array(
            'key' => 'published_date',
            'value' => array('20140401','20140405'),
            'compare' => 'BETWEEN',
            'type' => 'DATE'
        )
    )
));


if ($the_query->have_posts()) {

    echo '<ul>';
    while ($the_query->have_posts()) {
        $the_query->the_post();
        echo '<li>' . get_the_title() . '</li>';
    }
    echo '</ul>';

} else {
    echo 'Sorry! No Posts';
}

wp_reset_postdata();

The most important note on the codex.

The 'type' DATE works with the 'compare' value BETWEEN only if the date is stored at the format YYYYMMDD and tested with this format.

Answer 2

Here's what I ended up doing:

$args = array(  
    'post_type' => 'events',
    'posts_per_page' => -1,
    'meta_key' => 'start_date',
    'meta_value_num' => $yesterday,
    'meta_compare' => '<=',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'start_date',
            'value' => array($first_minute,$last_minute),
            'compare' => 'BETWEEN',
            'type' => 'NUMERIC',
        ),
        array(
            'key' => 'end_date',
            'value' => $yesterday,
            'compare' => '>=',
            'type' => 'NUMERIC',
        ),
        array(
            'key' => 'start_date',
            'value' => $yesterday,
            'compare' => '>=',
            'type' => 'NUMERIC',
        ),


    )
)

This gets the current month's events. I input variables for first minute of month, last minute of month, and "yesterday" which is 11:59:59 of yesterday.

Answer 3

Not sure what are you trying but seems logical to not return anything as you are selecting events that starts today or in the future (start_date >= $today) AND ends today or in the past (end_date <= $today)...

Answer 4

Alexandru-Florentin Popescu is right! Use your code another following:

$args = array(  
'post_type' => 'events',
'posts_per_page' => -1,
'meta_key' => 'start_date',
'orderby' => 'meta_value_num',
'order' => 'ASC',
'meta_query' => array(
    'relation' => 'AND',
    array(
        'key' => 'start_date',
        'value' => $today,
        'compare' => '<=',
        'type' => 'NUMERIC',
    ),
    array(
        'key' => 'end_date',
        'value' => $today,
        'compare' => '>=',
        'type' => 'NUMERIC',
    ),

));

You just need change key 'compare' in both arrays