WP Query and date format

Question

I've been using a CPT named "session" that has some date fields. Those date fields are stored via the CMB2 plugin with something like this :

$cmb->add_field( array(
    'name' => __('Begin :', 'cmb2'),
    'id' => $prefix . 'session_datebegin',
    'desc' => __( 'Beginning date of the session', 'cmb2' ),
    'type' => 'text_date',
    'date_format' => 'd-m-Y'
) );    

Now I want to display the sessions, ordered by the "closest" date. Here are the args used in my WP query :

$args = array( 
    'post_type' => 'session', 
    'posts_per_page' => -1,
    'meta_key' => 'session_datebegin', 
    'orderby' => 'session_datebegin', 
    'order' => 'ASC' 
    );

Let's say I have 3 dates : 22-04-2016, 20-04-2016 and 05-05-2016. The previous query gives : 05-05-2016, 20-04-2016, 22-04-2016. As you guessed, using "DESC" instead of "ASC" returned : 22-04-2016, 20-04-2016, 05-05-2016.

The date settings for my WP install are custom ones, under this form : d-m-Y.

I just can't figure out where the problem takes place : for "may", even there is a confusion somewhere with the US date format, 05-05-2016 will always be equal to 05-05-2016, won't it ?!

How can I make a proper sort with this date format ? Thanks

Answer 1

For those who experienced the same problem, I finally found a way to do the sort. First I created an extra CMB like this :

$cmb->add_field( array(
        'id' => $prefix . 'session_gooddatebegin',
        'type' => 'hidden',
    ) );

Then I created an action using save_post. If the CPT "session" is being updated, thanks to $update, I assume that the user selected a date in the CMB session_datebegin. Hence, we now are able to update the hidden date field, putting it to the correct form :

add_action( 'save_post', 'good_dates', 99, 3);

function good_dates( $post_id, $post, $update ) {

    if (get_post_type($post_id)!='session') {
        return;
    }

    //if the post is being updated
    if ($update==true) {

        //begin date 
        $datebegin = get_post_meta( $post_id, 'session_datebegin', true );

        //put to the expected form with date() and strtotime() 
        $datebegingood = date("mdY", strtotime($datebegin));

        //update the hidden variable
        update_post_meta($post_id, 'session_gooddatedeb', $datebegingood );
    }

}

Finally, the sort can be performed in the archive page using the same args as before :

$args = array( 
    'post_type' => 'session', 
    'posts_per_page' => -1,
    'meta_key' => 'session_datebegin', 
    'orderby' => 'session_datebegin', 
    'order' => 'ASC' 
    );

Answer 2

In a nutshell this is a poor format to store date.

You get results you get because 05 < 20 < 22 in that example. You are merely comparing strings and in these date strings day of the month is leading.

You wouldn't be able to easily cast it to MySQL DATE either since that one got YYYY-MM-DD format.

If you need sort you should store your dates in string–sortable format like DATE or store timestamps, which can be sorted as numbers (not human readable though).