I want to compare two timestamps in a meta_query instead of comparing two dates in the "Y-m-d" format, each of them stored separately in custom fields, but no success. The first timestamp is an event start date/time, the second is the local date/time, also as a timestamp. When I use them in my code, posts are displayed in a not understandable order. What is wrong here? The comparison of dates in the "Y-m-d" format works correctly.

function add_custom_post_type_to_query( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'post_type', array( 'facebook_events', 'event' ) );
        $query->set( 'meta_query', array(
            'relation' => 'OR',
            array(
                'key' => 'start_ts', //this is from facebook_events post type
                'value' => current_time( 'timestamp' ),
                'compare' => '>=',
                'type' => 'DATE',
            ),
            array(
                'key' => '_start_ts', //this is from event post type
                'value' => current_time( 'timestamp' ),
                'compare' => '>=',
                'type' => 'DATE',
            )
        ) );
        $query->set( 'orderby', 'meta_value' );
        $query->set( 'order', 'ASC' );
    }
}
add_action( 'pre_get_posts', 'add_custom_post_type_to_query' );

I want to compare two timestamps in a meta_query instead of comparing two dates in the "Y-m-d" format, each of them stored separately in custom fields, but no success. The first timestamp is an event start date/time, the second is the local date/time, also as a timestamp. When I use them in my code, posts are displayed in a not understandable order. What is wrong here? The comparison of dates in the "Y-m-d" format works correctly.

function add_custom_post_type_to_query( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'post_type', array( 'facebook_events', 'event' ) );
        $query->set( 'meta_query', array(
            'relation' => 'OR',
            array(
                'key' => 'start_ts', //this is from facebook_events post type
                'value' => current_time( 'timestamp' ),
                'compare' => '>=',
                'type' => 'DATE',
            ),
            array(
                'key' => '_start_ts', //this is from event post type
                'value' => current_time( 'timestamp' ),
                'compare' => '>=',
                'type' => 'DATE',
            )
        ) );
        $query->set( 'orderby', 'meta_value' );
        $query->set( 'order', 'ASC' );
    }
}
add_action( 'pre_get_posts', 'add_custom_post_type_to_query' );

• custom-post-types
• wp-query
• custom-field
• query
• meta-query

Share Improve this question asked Oct 24, 2017 at 19:09 IurieIurie 1,13144 gold badges2525 silver badges4646 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

This is the magic: with timestamps, insteed of 'type' => 'DATE' must be used 'type' => 'NUMERIC'.

Thanks! We ran into the same issue and this question helped a lot!

Alternate Use Case
We're looping through a custom post type called events (surprise, surprise, it stores events). It stores a timestamp in the meta field ev_start (event start time). We actually were able to just use the orderby value meta_value_num (as you can see meta_type is commented out). We also query the posts to make sure the event didn't already happen.

$current_time = current_time('timestamp');

$post_args = array(
   'post_type'         => 'events',
   'posts_per_page'    => 5, 
   'meta_key'          => 'ev_start',
   //'meta_type'       => 'NUMERIC',
   'orderby'           => 'meta_value_num', 
   'order'             => 'ASC',
   'meta_query' => array(
      array(
         'key'     => 'ev_start',
         'value'   => $current_time,
         'compare' => '>='
      )
   )
);

$posts = new WP_Query( $post_args );

Resources
WP_Query Code Reference