now idea why this isn't working, please point me in the right direction!

Here's my main query;

$events = new WP_Query( array(
        'posts_per_page' => -1,
        'meta_query' => array(
                'key' => '_event_date',
                'value' => time(),
                'type' => 'NUMERIC',
                'compare' => '<'    
    ) );

Each post has a custom field (_event_date) which is saved as a UNIX timestamp. I simply need to compare time() to this value to get posts with an older timestamp or posts with a newer timestamp. Here's the SQL that WP generates;

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( ( wp_postmeta.meta_key = '_event_date' AND CAST(wp_postmeta.meta_value AS SIGNED) < '1419072968' ) ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

Is this the correct way to query by a UNIX value? I've noticed there are other type settings, such as "date", "time" and so on. None of these seem to work.

Many thanks.

  • This looks fine to me (you should maybe use UNSIGNED instead of NUMERIC but it's not important). Are you sure you have _event_date meta data?! Try doing a SELECT * FROM wp_postmeta WHERE meta_key = '_event_date'; and see what you get... – bonger Dec 20 '14 at 11:48
  • @bonger Yes, I've done this query, it returns 3 results. 2 are older, and 1 is in the future. It's very odd, if the compare arg is "<" it returns two results, but it it's set to ">" it returns nothing! – Dan Dec 20 '14 at 12:54
  • Ah so it's almost working! Can only suggest maybe the one in the future isn't published? – bonger Dec 20 '14 at 13:32
  • Argh! As it turned out, the missing post was set as scheduled. I'm using the "fakerpress" plugin which generates dummy content, and I assume I picked a date range which meant some of the posts were scheduled to be published in the future. It's always an absolute pain to fall over such easy mistakes!! Thanks for your help. – Dan Dec 21 '14 at 11:42

Your Answer

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

Browse other questions tagged or ask your own question.