Time sort with meta_key using UNIX timestamp failing due to date difference (3 posts)

  1. Sean M.
    Posted 2 years ago #

    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](http://www.advancedcustomfields.com/add-ons/date-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' => 'thursday_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(
    		'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!

  2. sosukeinu
    Posted 2 years ago #

    not sure if this will help, and I'm also not entirely sure I'm understanding what you are trying to do, but as a general suggestion, you could add a custom field that will insert metadata in UNIX timestamp format (this takes into account date and time so you should be able to sort asc or desc on this). If your custom field is returning something other than a timestamp, you can use PHP to convert the value http://php.net/manual/en/function.date.php

    You can also filter the query with a function. For instance, here is a simple query filter that uses meta_query to order posts based on a custom field called post-weight, and excludes posts that have passed another custom field called expire-date:

    function my_get_posts( $query ) {
        $today = current_time( 'timestamp', 0 );
            $query->set( 'meta_key', 'post-weight' );
            $query->set( 'orderby', 'meta_value_num title' );
            $query->set( 'order', 'ASC' );
            $meta_query = array(
                    'key' => 'expire-date',
                    'value' => $today,
                    'compare' => '>='
            $query->set( 'meta_query', $meta_query );
        return $query;
    add_filter( 'pre_get_posts', 'my_get_posts' );
  3. WPWanderer
    Posted 1 year ago #

    Hey Sean,

    this is the exact issue I have having. Thought it was working then when I add an event a couple days later the time order gets messed up.

    Did you find a good way to get this done?


Topic Closed

This topic has been closed to new replies.

About this Topic