meta_query date and time comparisons

Question

I am trying to query my posts against the value of 2 custom meta_keys...

'_mdjm_event_date' formatted as Y-M-D (2015-05-13) '_mdjm_event_finish' formatted as H:i:s (23:35:00)

Here is my query...

$args = array(
                    'posts_per_page'    => -1,
                    'post_type'      => MDJM_EVENT_POSTS,
                    'post_status'      => 'mdjm-approved',
                    'meta_key'        => '_mdjm_event_date',
                    'meta_value'        => date( 'Y-m-d' ),
                    'meta_compare'    => '<=',
                    'meta_type'      => 'date',
                    'meta_query'        => array(
                                            'relation'  => 'AND',
                                                array(
                                                    'key'       => '_mdjm_event_finish',
                                                    'value'   => date( 'H:i:s' ),
                                                    'compare'   => '>',
                                                    'type'     => 'numeric',
                                                ),
                                            ),
                    );

What I am trying to return are all posts that have an event date before or equal to today. If the event date is today, then the finish time should be less than the current time but the code above is not providing the desired results. I have 8 events that meet the criteria, but I am only getting 2 returned.

Any help appreciated

Thanks

Answer 1

You are mixing up an older meta syntax with the newer one. I'd suggest you clean that up for readability and for potential compatibility in the future. (WordPress does seem to parse the mixed query correctly though)

$args = array(
  'posts_per_page'    => -1,
  'post_type'         => MDJM_EVENT_POSTS,
  'post_status'       => 'mdjm-approved',
  'meta_query'        => array(
    'relation'  => 'AND',
    array(
        'key'       => '_mdjm_event_date',
        'value'     => date( 'H:i:s' ),
        'compare'   => '<=',
    ),
    array(
        'key'       => '_mdjm_event_finish',
        'value'     => date( 'H:i:s' ),
        'compare'   => '>'
    ),
  ),
);

The one other thing I changed was the type casting to numeric of the date value. I am pretty sure that was tripping up your query. If you run SELECT CAST('23:20:36' as signed) in a MySQL console (PHPAdmin, command line, etc) you will see that what gets returned in just "23". That isn't going to match.