Query between dates using Date Picker fields

I’m looking to replace an events plugin that i have on my website currently. We realize we don’t need a full blown calendar and things like that, we just need to be able to enter in Events with a start and end date and also custom times. I made custom fields for start date and end date using the Advanced Custom Fields -> Date Picker fields. Naturally, it’s possible that an event may span over multiple months.

So if an event spans from May to July, when I am in June, I want this event to appear.

We have a widget on our homepage that loads in the events by month and year. For example, it shows all of the events in may. What I am having problems getting it to do is in the case where an event starts in May and ends in July, i want that event to also show up in the month of June because that event is still going on. Maybe there is a Before/After comparison thing i can do. I’ve updated wordpress to the latest version as of today.

I’m not quite sure how i should handle the date query using the custom fields. Is there a better way that I should do this?

I wonder if you may have some suggestions or can help me out with the query I’m trying to write.

I’ve tried another approach and that’s writing a SQL query to grab the posts and display them. I am running into the same issue, but maybe some further tweaks of this query we can get this to work.

The values of $startday and $endday are 20140601 and 20140630. I want to grab all of the events that may occur between those two dates. I see the way I have it written now if the end date is beyond 20140630 then it won’t display. What are my options here because some events will span multiple months. I also think the date values are stored as strings, so do i need to convert them or do something to those values to make behave like real date fields? Is there a way i can split this into two queries. The first query would query all of the events and then other query could get a subset of the initial query results?

$querystr = "
    SELECT *
    FROM $wpdb->posts wposts, $wpdb->postmeta metastart, $wpdb->postmeta metaend
    WHERE (wposts.ID = metastart.post_id AND wposts.ID = metaend.post_id)
        AND (metastart.meta_key = 'event_start_date' AND metastart.meta_value > $startday )
        AND (metaend.meta_key = 'event_end_date' AND metaend.meta_value <= $endday )
        AND wposts.post_type = 'events'
    AND wposts.post_status = 'publish'
    ORDER BY metastart.meta_value ASC
 ";

I have also tried this, but the problem with this query is that if I have an event that starts in May and ends in July, the event won’t show up if I am on the June page (which it should because it falls within that date range).

Has anyone every dealt with an issue like this?

$all_events = array (
    'post_type' => 'events',
        'posts_per_page' => 50,
        'status' => 'published',
        'meta_key'      => 'event_start_date',
        'orderby'       => 'meta_value_num',
        'order'         => 'ASC',
        'meta_query' => array(
        array(
            'key'       => 'event_start_date',
            'compare'   => '>=',
            'value'     => $startday,
        ),
            array(
            'key'       => 'event_start_date',
            'compare'   => 'BETWEEN',
            'value'     => array($startday, $endday),
        ),

             array(
            'key'       => 'event_end_date',
            'compare'   => '>=',
            'value'     => $endday,
        )
    ),
);

This is even hard to explain.

First, I have a widget that displays events by month and year. Any event that occurs between May 1 and May 30 2014 for example should appear on the May section. This almost works. Where it crumbles is when an event spans over mulitple months, like an event that would go from May to July and if i am on the June page, it wont show this event.

How can i modify the query to get my query to work in this way?

Kinda shifted gears a bit. I came up with an idea to store the range of dates between the two dates selected into a new custom field. I made a textarea custom field and wrote a function that grabs the range of the two dates and the saves it into this field.

I’m storing it as a string like this:

20140506,20140507,20140508,20140509

This event starts on may 6 2014 and ends on may 9 2014.

The way i imagined this working is I’d store the range in the textarea and then create another range via the code, not stored in the database, the new range would from first day of the month to the last day of the month. Then I was gonna send the string or array, whatever would work and do a meta query using compare “IN” to get any matches. This doesn’t seem to be working.

I make the range like this - the function takes the dates and then i convert the array it makes to a string.

$aryDates = createDateRangeArray($startday, $endday);
$date_str = implode(",", $aryDates);

So, then i tried this:

$all_events = array (
    'post_type' => 'events',
		'posts_per_page' => 50,
		'status' => 'published',
		'meta_query' => array(
		'relation' => 'OR',
		array(
        'key' => 'auto_event_date_span',
        'value' => $date_str,
        'compare' => 'IN',
        'type' => 'NUMERIC'
    )
 )
);

Is there a way i can do what i describle with wp_query and meta_query OR do i need to write a custom query?

Here is the solution on wordpress.org.

Thanks “keesiemeijer” for your assistance!