Display upcoming events in the next 30 days by querying posts with timestamp in custom meta

Question

I have a custom post type "event" which stores a custom meta as a timestamp ($event_date). The date is always in a dd-mm-yyyy format, so I can generate a unix timestamp from this key. The timestamp doesn't match the pubdate, it's just any date set in the future.

I'd like to make a wp query to list all upcoming posts (events) ie comparing present time with these timestamps and ordering the posts accordingly (show upcoming first, closer to present date). Pubdate should be disregarded; if date is ambiguous (if two events have same $event_date), then order them alphabetically or whatever.

I would like also to be able to query only the events occurring in the next 30 days.

I'm going to try this, but I'm wondering if there's a better way to do, because I don't know how to get only the posts scheduled within 30 days from now:

query_posts(array(
    'posts_per_page' => 30,
        'meta_key' => 'event_date',
    'meta_value' => date(Y-m-d), // I could use directly unix timestamps
    'meta_compare' => '>',
        'orderby' => 'meta_value',
        'order' => 'ASC'
));

this should sort the posts with the posts occurring in the future first... howerver that doesn't necessarily mean they will be 30 days from now; suppose I want already to publish something that is going to happen in 60 days or next year... How to set a costraint for the query to display only the posts occurring in the next 30 days or any set amounts of days/period?

Answer 1

I recently did exactly the same, you'll have to use custom query:

                 $date = time()-86400; /* today */
                 $querystr = "
                    SELECT $wpdb->posts.* 
                    FROM $wpdb->posts, $wpdb->postmeta
                    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
                    AND $wpdb->posts.post_status = 'publish' 
                    AND $wpdb->postmeta.meta_key = 'event_date' 
                    AND $wpdb->postmeta.meta_value > " . $date . " 
                    ORDER BY $wpdb->postmeta.meta_value ASC
                 ";


                 $pageposts = $wpdb->get_results($querystr, OBJECT);
                 foreach ($pageposts as $post):
                 /* etcetera */

Hope it helps...

Code for changing dd/mm/yyyy to unix timestamp:

$date = explode ('/',$date);
$date = $date[1].'/'.$date[0].'/'.$date[2]; // dd/mm/yyyy to mm/dd/yyyy
$date  = strtotime($date); /* UNIX TIMESTAMP */