Order by date in post meta

Here’s a quick tip to order posts by a date stored in custom fields (postmeta).

The problem

I have a custom field called ‘event-date’ in which, obviously, a date value is stored. Now i want all the posts sorted by that custom field value. How do i do it?

To order posts by custom field, we’d do something like:

<?php
query_posts(array(
	'meta_key'=> 'event_date',
	'orderby'=> 'meta_value',
	'order' => 'DESC'
));
?>

But this won’t work since the value of the postmeta is taken as string and sorted accordingly, which can given unexpected results.

The solution

  1. Add the following code in functions.php of your theme (or at any appropriate place, if you are writing a plugin):
    <?php 
    /* sorts the post based on a date value in custom fields 
     * check: http://webdeveloperswall.com/wordpress/order-by-date-in-post-meta
    */
    function wdw_query_orderby_postmeta_date( $orderby ){
    	$new_orderby = str_replace( "wp_postmeta.meta_value", "STR_TO_DATE(wp_postmeta.meta_value, '%m/%d/%Y')", $orderby );
    	return $new_orderby;
    }
    ?>

    I’ve my date format as ‘mm/dd/yyyy’, if yours is something else, don’t forget to change ‘%m/%d/%Y’ above with a suitable value.

  2. Next, our query_post code will remain the same, but we will add a filter just before this code:
    <?php
    //the filter to sort by date in custom field
    add_filter( 'posts_orderby', 'wdw_query_orderby_postmeta_date', 10, 1);
    query_posts(array(
    	'meta_key'=> 'event_date',
    	'orderby'=> 'meta_value',
    	'order' => 'DESC'
    ));
    ...
    ...
    ...
    //once done, dont forget to remove that filter
    remove_filter( 'posts_orderby', 'wdw_query_orderby_postmeta_date', 10, 1);
    ?>

Thats a quick, dirty way of getting it done! 🙂
That’s all here.

Published by

Chandan Chaudhary

Chandan is a WordPress guy. A programmer at heart. He loves building community sites with WordPress and BuddyPress.

6 thoughts on “Order by date in post meta”

    1. I don’t see if there should be any difference with custom post type. You obviously need to pass different parameters for query_post (like ‘post_type’=>’my_custom_posttype’) but the sorting method remains the same.

  1. i use it on one of my website, but i have got one trouble, my post are order only by the “day”

    My date format is differents cause it’s in french format d/m/y so i change the function in :
    function wdw_query_orderby_postmeta_date( $orderby ){
    $new_orderby = str_replace( “wp_postmeta.meta_value”, “STR_TO_DATE(wp_postmeta.meta_value, ‘%d/%m/%Y’)”, $orderby );
    return $new_orderby;
    }
    Any idea for solve my problem ?
    Thanks you

    1. It should work. I dont see any error here. Make sure you’ve added the filter before wp_query/query_posts and make sure the function is reachable/is in proper file. That’s all i can say

Leave a Reply

Your email address will not be published. Required fields are marked *