Time sort with meta_key using UNIX timestamp failing due to date differences

Question

I'm dealing with the following issue trying to sort a custom post type by a meta key created using the Advanced Custom Fields plugin Date and Time Picker add-on. The add-on creates a jQuery datepicker for users to set times in the dashboard. By default, an option save_as_timestamp is set to true, so the data in the back-end is set as a Unix timestamp/epoch time.

I'm trying to sort a series of events (all occurring on one day) by time. The problem I'm having is, if all the events were added on one day, then they sort correctly (e.g. a 9:00 am event would come first, 5:00 pm would come last). However, if a day or two later, my boss adds a new event to the list, it will show up as the final event, event if it were a 9:00 am event, because it's date is different. My post type query is as follows:

<?php $args = array(
    'post_type' => 'event',
    'posts_per_page' => -1,
    'nopaging' => true,
    'post_status' => 'publish',
    'meta_key' => 'start_time',
    'orderby' => 'meta_value_num',
    'order' => 'ASC'
);
$my_query = new WP_Query( $args );
while( $my_query->have_posts() ) : $my_query->the_post(); ?>

In my estimation, I have two options, change the save_as_timestamp option to false, so that no date is associated with the time. Problem is I'd have to go in and set the times again on all the events and, still, I'd have to manage a meta_query that can sort PM times after AM times, because, when I do this, it still sorts a 9:00 AM time after a 5:00 PM time. I've played with using meta_query and setting type to 'TIME' but still no luck.

Second option, I imagine I could format the timestamp in a manner where the date is stripped out using some combination of the date, time or DateTime functions in PHP, but I'm at a loss as to how to do this correctly. I tried added the following to the query, but I'm not experienced with meta_query at all:

'meta_query' => array(
    array(
        'key' => 'start_time',
        'value' => date('Hi', time()),
        'compare' => '>='
    )
)

Is there any way to do this or would I have to use some function or pre_get_posts filter that will format the meta_key so that I can access it as a formatted variable before the loop begins? Any help is greatly appreciated!

Answer 1

I will caveat this by saying that I am still not 100% sure I know what you are doing, but I believe the following will sort your results by time only assuming that your meta values are proper Unix timestamps.

function orderby_time_only($orderby) {
  remove_action('posts_orderby','orderby_time_only');
  return preg_replace('|(.*)\s(.*)|','DATE_FORMAT(FROM_UNIXTIME($1),"%k%i") $2',$orderby);
}
add_filter('posts_orderby','orderby_time_only');

$args = array(
    'post_type' => 'event',
    'posts_per_page' => -1,
    'nopaging' => true,
    'post_status' => 'publish',
    'meta_key' => 'start_time',
    'orderby' => 'meta_value',
    'order' => 'ASC'
);
$my_query = new WP_Query( $args );

The date itself is ignored so if you have multiple dates mixed together that should not be, they will get scrambled.

The add_filter line should come immediately before the query you want it to influence but the callback can be defined elsewhere.