I've been reading alot about how to order posts by a meta_value, but I can't seem to get it to work for me.

My meta_key is called date, and it is stored as yyyy-mm-dd.

Here's where I've gotten to so far:

$args = array(
    'posts_per_page' => -1,
    'meta_key' => 'date',
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'post_type' => 'post',
    'post_status' => 'publish'         
if (have_posts()) while (have_posts()) : the_post(); 

As far as I understand, this should give me what I want - but doing this on the index.php page doesn't seem to change the query. The posts seem to still be ordered by post_date rather than my date meta value, which is different.

What I want to do is override or change the query on the posts and archive pages so that it will order by my meta_key. It is present on every post so there is no issue with what to do if it's not there.

Have I got the arguments right, and if so, what more do I need to do to get this working?

Any help appreciated


I tried using WP_Query() with these arguments and printed out the object. The request method of the object ordered by menu_order ASC and completely ignored my meta_value_num argument. So this is why the ordering doesn't work, but the rest of the query was fine. Any ideas why WP is ignoring my order args?

Another Update

OK, narrowing it down. The date is stored as text in the postmeta table, so trying to order by it and expecting MySQL to treat it like a date is a bit silly. To that end, I wrote my own query which gets what I need:

SELECT wp_posts.post_title,
       STR_TO_DATE(m.meta_value, '%Y-%m-%d') AS m_date
FROM wp_posts  
LEFT JOIN wp_postmeta m ON ( wp_posts.ID = m.post_id AND m.meta_key = 'date')
WHERE post_status='publish' AND post_type='post'

Notice the STR_TO_DATE() call? This casts the string as a date, allowing MySQL to order by it correctly. Phew. Now to edit my loop....

Alternatively, does anyone know a hook I could use to edit the query in place as it's called? It would be alot more efficient than running two queries.

  • Have you tried storing the date as a unix timestamp?
    – mor7ifer
    May 24, 2012 at 13:33
  • nope, will give it a try if you think it will help
    – jammypeach
    May 24, 2012 at 13:35
  • The date with hyphens must be the culprit. Try meta_value instead of meta_value_num cuz with those hyphens, it's a string. May 24, 2012 at 13:42
  • @RutwickGangurde just tried, sorry but same as before. I think you're right though, it must be to do with this custom date being stored as text rather than a date. Just playing with the DB at the moment...
    – jammypeach
    May 24, 2012 at 13:44
  • Yep, I bet storing the date right will solve your problem... May 24, 2012 at 14:02

1 Answer 1


So I figured out how to get what I want, but it's still not perfect. The problem revolved around how my custom meta value was being stored - basically as a string.

I needed to tell MySQL that it was a date, so it could be used to order the posts. To this end, I used a custom query:

    $posts = $wpdb->get_results( 
       "SELECT wp_posts.*, 
        STR_TO_DATE(m.meta_value, '%Y-%m-%d') AS m_date 
        FROM wp_posts 
        LEFFT JOIN wp_postmeta AS m ON(wp_posts.ID=m.post_id AND m.meta_key='date')
        WHERE post_status='publish' AND post_type='post'
        ORDER BY m_date DESC"

if ($posts):
    global $post;
    foreach ($posts as $post):
        echo get_the_title(); //finally in the right order!

This uses the MySQL function STR_TO_DATE() to cast the meta value as a date, then use it to order the posts in the ORDER BY clause.

So this is all well and good but it would be much better if I could hook into the query before it's run and alter the SQL just this little bit, I could make a big saving in terms of queries. The site is a small one so it's not a massive concern, but every little helps.

If someone knows how to do that, I'd happily accept that answer instead of my own.




  • Is there any particular reason you're avoiding the unix date format?
    – mor7ifer
    May 24, 2012 at 14:43
  • @m0r7if3r yes, the field is filled out with a js datepicker and having a unix timestamp might be confusing to the user even if they don't need to set it by hand. however, I've tried it and it's another solution to this problem: sqlfiddle.com/#!2/d77da/3
    – jammypeach
    May 24, 2012 at 16:19
  • str_to_time() is your friend.
    – mor7ifer
    May 24, 2012 at 19:02
  • @m0r7if3r that is certainly another option, however it gives birth to another question - how to change the meta value (i.e running str_to_time() on it) before it's saved on wp-admin. And the effect is still the same - I'm going to require another query regardless, the only difference is that I can use query_posts() instead of writing the SQL myself. If you have a better way, feel free to post an answer and if it's good I'll gladly accept it over my own
    – jammypeach
    May 25, 2012 at 9:49
  • You should be able to run it during your data validation and have the same number of queries as if you didn't convert it.
    – mor7ifer
    May 25, 2012 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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