wp query - How to create better WP_Query to look for date time which is anywhere between two meta values?

The following arguments are used for WP_Query to check if a reservation for a game can be created. Meta input fields for post are created using ACF DateTime Picker and set to Y-m-d H:i:s

$start_date = '2019-08-31 12:15:00';
$end_date = '2019-08-31 12:20:00';

$args = array(
    'post_type' => 'rezervacija',
    'posts_per_page' => -1,
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'relation' => 'OR',
            array(
                'key' => 'start_date',
                'value' => array($start_date, $end_date),
                'compare' => 'BETWEEN',
                'type' => 'DATETIME'
                ),
            array(
                'key' => 'end_date',
                'value' => array($start_date, $end_date),
                'compare' => 'BETWEEN',
                'type' => 'DATETIME'
                )
            ),
        array(
            'key' => 'location', // Look also for specific playground, not important for this question
            'value' => 3,
            'compare' => '='
            )
        )
    );

$query = new WP_Query($args);

This code generally works, but not fully as expected. If there is already reservation created with a start date set to 12:00 (2019-08-31 12:00:00) and end time 14:00 (2019-08-31 14:00:00) and then if a new user selects time 12:15, and end time 13:30, in that case since it looks for those values separately the query won't find anything but it should. So generally this query won't find data properly when the time is somewhere inside of previous reservation times.

How can I modify this query to search for posts whose $start_date and $end_date are anywhere between values start_date and end_date?

The following arguments are used for WP_Query to check if a reservation for a game can be created. Meta input fields for post are created using ACF DateTime Picker and set to Y-m-d H:i:s

$start_date = '2019-08-31 12:15:00';
$end_date = '2019-08-31 12:20:00';

$args = array(
    'post_type' => 'rezervacija',
    'posts_per_page' => -1,
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'relation' => 'OR',
            array(
                'key' => 'start_date',
                'value' => array($start_date, $end_date),
                'compare' => 'BETWEEN',
                'type' => 'DATETIME'
                ),
            array(
                'key' => 'end_date',
                'value' => array($start_date, $end_date),
                'compare' => 'BETWEEN',
                'type' => 'DATETIME'
                )
            ),
        array(
            'key' => 'location', // Look also for specific playground, not important for this question
            'value' => 3,
            'compare' => '='
            )
        )
    );

$query = new WP_Query($args);

This code generally works, but not fully as expected. If there is already reservation created with a start date set to 12:00 (2019-08-31 12:00:00) and end time 14:00 (2019-08-31 14:00:00) and then if a new user selects time 12:15, and end time 13:30, in that case since it looks for those values separately the query won't find anything but it should. So generally this query won't find data properly when the time is somewhere inside of previous reservation times.

How can I modify this query to search for posts whose $start_date and $end_date are anywhere between values start_date and end_date?

• wp-query
• meta-query

Share Improve this question asked Aug 28, 2019 at 13:59 Ivan TopićIvan Topić 52111 gold badge66 silver badges1919 bronze badges 2

• That location meta key looks like it could be a custom taxonomy instead, which would be orders of magnitude faster. Look up in the ACF docs how to use a taxonomy rather than post meta as the storage for a field. Otherwise avoid searching for posts using post meta values at all costs, it's stupendously slow/expensive and not what post meta was designed for – Tom J Nowell ♦ Commented Aug 28, 2019 at 15:38
• Thanks, I have researched a little about this, although this is an older website I will pay attention in the future projects. – Ivan Topić Commented Aug 29, 2019 at 8:43

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You might want to do this for querying between two dates:

$query = new WP_Query(array(
                        'post_type' => 'rezervacija',
                        'posts_per_page' => -1,
                        'post_status' => 'publish',
                        'meta_query' => array(
                                'relation' => 'AND',
                                array(
                                    'key'     => 'start_date',
                                    'value'   => current_time('Ymd'), //change this according to your need.
                                    'compare' => '>=',
                                ),
                                array(
                                    'key'     => 'end_date',
                                    'value'   => current_time('Ymd'), //change this according to your need.
                                    'compare' => '<=',
                                ),

                            ),
                    ));

    if ($query->have_posts()) : 
        while($query->have_posts()) : 
            $query->the_post();
            the_permalink(); 
            the_title();
        endwhile;  
    endif;