Custom Field Date Problem

Question

I have a custom field called expiry_date with a value of let's say 2010-12-11 00:00:00.

What I would like to do is, in my query loop, only return posts with this expiry_date custom field attached to posts and the following condition:

• The date in the expiry_date custom field has to be within 24 hours of expiring, if it past the current date, the post is 'expired' and will not show in the loop or if the post is within 25 hours of expiring, it will also not show etc.

Any idea at all please

Answer 1

I agree with Denis, I don't like using custom fields for this kind of stuff, but I'm not aware of any other way yet...

What you what can be done by altering the SQL query for the loop, something like this:

add_filter('posts_join', 'my_post_filter_join');
function my_post_filter_join($join) {
  global $wpdb;
  if(is_home() || is_category() || is_search())
    $join .= " LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)";
  return $join;
}

add_filter('posts_where', 'my_post_filter_where');
function my_post_filter_where($where) {
  global $wpdb;

  // current date in unix format
  $current_time = current_time("timestamp");

  // exclude any posts older than your expiry date set in the custom field
  if(is_home()|| is_category() || is_search())
    $where .= " AND ($wpdb->postmeta.meta_key = 'expiry_date' AND $wpdb->postmeta.meta_value > '{$current_time}')";
  return $where;
}

I used unix timestamps here instead of a formatted date string, you should do that too, it's easier to handle. For the admin interface you can use a datepicker and convert the date to a timestamp when the post is saved...

Answer 2

You're looking for a BETWEEN clause, which isn't supported off the top of my head.

You might get around this by using two meta clauses, one >= and the other <=, both on the same meta key. WP is likely to join the meta table twice, which isn't great for performance, but it'll work.

Alternatively, use the posts_join and the posts_where filters.

Oh, and... in case it needs reminding, your queries are going to be slow if you've any significant amount of data, due to the lack of index on the meta value. I've no idea of what your use case might be about, but it sounds like one where an extra field to the originating table is the proper way to go.