how to compare different timestamps in wp-query for events custom post types?

Question

I have a custom post type with custom metaboxes (as seen on wptheming.com) and would like to make a query to show upcoming events, say in the sidebar, where past dates are left out.

But I need a value of the current time to compare with the events start time.

Right now i got a current time from current_time('mysql') that looks like this:

2012-02-16 13:15:31

And a start time from the meta that looks like this:

201108121100

How can I make the two dates comparable? Can i do it inside the query or is there another solution?

Below is my query so far (with the "value" left blank, and the time-stamps echoed out):

<?php $args = array( 
    'post_type'     => 'event',
    'meta_key'      => '_start_eventtimestamp',
    'orderby'       => 'meta_value_num',
    'meta_query'    => array(
                         array(
                                'key'     => '_start_eventtimestamp',
                                'value'   => '',
                                'compare' => '!=',
                                'type'    => 'NUMERIC'
                            )
                        ),
    'order' => 'ASC',
    'posts_per_page' => 10,
    );

$events = new WP_Query( $args );

if ( $events->have_posts() ) :
echo '<ul>';
while ( $events->have_posts() ) : $events->the_post();
echo '<li><a href="' . get_permalink() . '">' . current_time('mysql') . '</a></li>';
echo '<li><a href="' . get_permalink() . '">' . get_post_meta( $post->ID, '_start_eventtimestamp', true ) . '</a></li>';
endwhile;
echo '</ul>';

endif;

?>

Below is the code in my function.php. (It works fine in the archive-event.php but not anywhere else):

function ep_event_query( $query ) {

// http://codex.wordpress.org/Function_Reference/current_time
$current_time = current_time('mysql');
list( $today_year, $today_month, $today_day, $hour, $minute, $second ) = split( '([^0-9])', $current_time );
$current_timestamp = $today_year . $today_month . $today_day . $hour . $minute;

global $wp_the_query;

if ( $wp_the_query === $query && !is_admin() && is_post_type_archive( 'event' ) ) {
    $meta_query = array(
        array(
            'key' => '_start_eventtimestamp',
            'value' => $current_timestamp,
            'compare' => '>'
        )
    );
    $query->set( 'meta_query', $meta_query );
    $query->set( 'orderby', 'meta_value_num' );
    $query->set( 'meta_key', '_start_eventtimestamp' );
    $query->set( 'order', 'ASC' );
}

}

add_action( 'pre_get_posts', 'ep_event_query' );

How do i do this?

Answer 1

Looks like a unix timestamp is being stored in the post meta - so you should compare with a unix timestimp, not a MySQL's timestamp.

To get the current time as a timestamp you should use the following code:

current_time( 'timestamp' );

Answer 2

you can do what Matth_eu suggested, or you can use the PHP FUNCTION date like so :

$Now =  date("Ymdhi");

Beware of the "h" parameter - you need to see how your META is getting the time - leading "0" , 12/24 hours etc (was not clear from your example)

See here for more info about this function:

http://php.net/manual/en/function.date.php

I am enclosing a working function that I have wrote some time ago that is very similar to what you need - it compares meta data of a date (format YYYY-MM-DD) with today and retrieves "upcomming" events to show in a widget. I hope it will help you understand how it works.

function dl_upccoming(){
$orig_post = $post;
global $post;
$todaysDate = date('Y-m-d'); // set today's date to check against custom field
$today = date('d');//testing
$post_no = 8;//how many we want
// query posts
$my_query = new WP_Query('posts_per_page='.$post_no.'&meta_key=_sub_deadline&meta_compare=>=&meta_value=' . $todaysDate . '&orderby=meta_value&order=ASC');
if( $my_query->have_posts() ) {
$pospstring = '<div id="upcomming"><ul class="sidebar-widget sidelists">';
while( $my_query->have_posts() ) {
$my_query->the_post();

$pospstring .= '<li><a href="' . the_permalink() . '" rel="upcomming" title="Deadline :' . get_post_meta($post->ID, '_sub_deadline', true) .' | ' . the_title() . ' - ">';
$pospstring .=  the_post_thumbnail('tooltip-50');
$pospstring .= '<span><' .  the_title() .'</span>';
$pospstring .= '<span>Deadline : ' .  get_post_meta($post->ID, '_sub_deadline', true) . '</span></a></li>';
}
}
$pospstring .=  '</ul></div>';

echo $poststring;
$post = $orig_post;
wp_reset_query();
}