Using date stored as custom field to filter posts displayed in admin

Question

This seems like a fairly common problem, but I'm posting this as I've yet to find a solution to my problem here on WP Answers - any help is HUGELY appreciated!

Ok - so I have a custom post type of 'events' where I capture the date of the event using a custom field and store it as a unix timestamp. Filtering posts on the front end is not a problem using the following code to show future events only:

$events = new WP_Query( 'post_type=events&posts_per_page=-1&meta_key=date_value&orderby=meta_value&meta_compare=>=&meta_value='.time().'&order=ASC' );

I've tried to filter the results in the admin in the same way using a pre_get_posts filter:

function events_pre_get_posts($query) {
  if (is_admin()) {
    if (isset($query->query_vars['post_type'])) {
      if ($query->query_vars['post_type'] == 'events') {
          $query->set('meta_key', 'date_value');
          $query->set('orderby', 'meta_value');
          $query->set('meta_compare', '>=');
          $query->set('meta_value', time());
          $query->set('order', 'ASC');
      }
    }
  }
}
add_filter('pre_get_posts' , 'events_pre_get_posts');

But in the admin this code just makes all my posts disappear!

I'm also trying to find a way to show past events in the back end and I'm really stuck there too! I not sure the best way to do this - perhaps a toggle on the 'events' posts page to view past / future events.

Help!!

Answer 1

Second try: ;-)

pre_get_posts runs AFTER parse_query(). parse_query() transforms 'meta_key' etc. into 'meta_query'. get_posts() doesn't seem to react on 'meta_key' etc. directly. So try adding $query->parse_query() after your $query->set() calls or use filter that runs before parse_query (edited, the parse_query() filter doesn't work, either - it runs too late).

Edit: to do the switch:

function my_timeswitch() {
    $operators = array(
        '>=' => 'future',
        '<=' => 'past'
    );
    ?>
    <select name='meta_compare'>
        <?php
            foreach ($operators as $k => $v) {
                printf( "<option %s value='%s'>%s</option>\n", selected($_GET['meta_compare'], $k, false), esc_attr($k), __($v));
            }
        ?>
    </select>
    <?php
}
add_action('restrict_manage_posts', 'my_timeswitch');

This may work just on it's own. If not, you'll have to change the name to something else, then register a new public query variable via the query_vars filter and then react on it via the parse_query filter.

Answer 2

Use current_time('mysql') instead of time().

time() returns a unix timestamp (that is an integer), current_time('mysql') returns a mysql timestamp.