0

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!

  • What is the result you want? It likes you might be trying to query by a particular day? Do you have a post type for each day of the week? – s_ha_dum Nov 10 '13 at 19:01
  • this doesn't really make sense. if date is correctly set via a date/time picker, why should it matter in what order that date/time is chosen? – Milo Nov 10 '13 at 19:11
  • For the purposes of this query, I'm just trying to get all of posts of one post type. @Milo, you would think the date the event is added wouldn't matter, but it does. As the datepicker sets the timestamp in epoch time, if I add a 9:00 AM event right now and echo the timestamp it's "1384074000," while a 5:00 PM event from the other day has a timestamp of "1383843600." Seeing that date is a factor (that 1384074000 is higher than 1383843600), this 9:00 AM event will appear last in the list. – Sean M. Nov 10 '13 at 19:34
  • So you see why I'd like to format the timestamp before doing the sort, because it needs to sort the posts by time only. It can't have a event w/timestamp of a later date, but an earlier time sort after another. What I want is to strip out the date and echo a 24 hour time for the sort. That's why I tried date('Hi'), but to no effect. – Sean M. Nov 10 '13 at 19:41
  • So you are doing something like what I suspected. You should be able to get the query working but will probably need a filter. I can look into it later. – s_ha_dum Nov 10 '13 at 21:03
0

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.

  • Thanks for the help! That definitely has the events sorting in a manner where date is completely ignored, but it looks like it's a numerical sort, where 3 pm comes first and an 11:00 am event comes last as it's the highest hour number in the queried events. – Sean M. Nov 11 '13 at 3:54
  • The %H format specifier instead of %k. – s_ha_dum Nov 11 '13 at 4:26
  • This breaks now on my implementation of a new theme (WP Version 4.1) If you have a look at the page overview there will be an sql error. Looked at the database everything looks right, also the WP check can't find a problem in there. – DominikAngerer Jan 18 '15 at 16:40
  • What is the error, @DominikAngerer? You may need to start a new question. – s_ha_dum Jan 18 '15 at 19:26
  • I simply added the function orderby_time_only and add_filter to my functions.php everything worked fine till the update to 4.1 - after that I faced an SQL Exception on the Page Overview Page in the admin area - (only there) after I turned on the debug mode from WP. After deleting the function + add_filter the problem was solved. I can reproduce this problem on every WP installation with 4.1 - don't need a new question but had to add it because it broke my template :) It meant something like: Check your SQL syntax near '_UNIXTIME($1),"%k%i")' so it was clear for me that this caused the issue – DominikAngerer Jan 19 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.