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.

Here is what I have so far:

$all_events = array (
'post_type' => 'events',
    'posts_per_page' => 50,
    'meta_query' => array(
    array(
        'key'       => 'event_start_date',
        'compare'   => '>=',
        'value'     => $startday,
    ),
     array(
        'key'       => 'event_end_date',
        'compare'   => '<=',
        'value'     => $endday,
    )
    ),
);

This almost works. It will display the events if the start and end date are within the same month, but I don't see the events that end in another month or two months from now.

I pass the month and year to the page to filter the list by month and year...I'm wondering if there is a better compare I should use for the end date so it sees those events that may have started in the month selected but also end at a much later time.

Any suggestions would be greatly appreciated.

UPDATE #1:

After some experimenting, I might have gotten a solution. Take a look at 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 one doesnt work either. I'm able to get and display the events, but some of the events don't show up exactly. The most difficult part is that an event may span over several months. It might start on April 1 and go to July 15, for example. The problem I am having is getting these events to show in the months of May and June.

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. 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
 ";

UPDATE #3

I've made a little progress and i think i am almost there to the finish line with this, but I need a little bit of help with this last bit.

The solution we came up with seems a little whacky to me, but I think we can get this to work. Here's what I did. I made two fields, one for start date and one for end date. I made an additional custom field to hold the range of dates in. They go in a comma-delimited string. Then I query that field to see if any of the dates in the span of dates from the first of the month to the end of month match. That's the part I am stuck on now.

Tell me if i have this setup correctly.

First in my functions.php file, I setup two functions to handle creating the date range and then saving the range to the custom field (which will be hidden by default so the user won't even know its there).

Make a Date Range Function - i found this somewhere on the internet, probably this website.

function makeDateRangeArray($startday,$endday)
{
$aryRange=array();

$iDateFrom=mktime(1,0,0,substr($startday,5,2), substr($startday,8,2),substr($startday,0,4));
$iDateTo=mktime(1,0,0,substr($endday,5,2), substr($endday,8,2),substr($endday,0,4));

if ($iDateTo>=$iDateFrom)
{
    array_push($aryRange,date('Ymd',$iDateFrom)); // first entry
    while ($iDateFrom<$iDateTo)
    {
        $iDateFrom+=86400; // add 24 hours
        array_push($aryRange,date('Ymd',$iDateFrom));
    }
}
return $aryRange;
}

Then I made a function to update the textarea field with the array converted to a string of date ranges. If my start date is jan 1 and end date is jan 15, I'll get 20140101 - 20140115 placed into this extra field. I'll later query this field instead of the two date picker fields.

function update_event_date_range( $post_id ) {

    $slug = 'events';

    // If this isn't an 'event' post, don't update it.
  if ( $slug != $_POST['post_type'] ) {
      return;
  }

    // Qry the post by ID
    if ( isset( $post_id ) ) {

        // Get the date field values
        $tempstartday = get_field( "event_start_date", $post_id);
        $tempendday = get_field( "event_end_date", $post_id);

        // setup date vars
        $startday = date('Y-m-d', strtotime($tempstartday)); // keep hyphens
        $endday = date('Y-m-d', strtotime($tempendday)); // keep hyphens

        // Make the array
        $DateRangeAry = makeDateRangeArray($startday, $endday);
        $date_str = implode(', ', $DateRangeAry);

    // Update the new post field with the date span
    update_post_meta( $post_id, 'auto_event_date_span', $date_str );

}
}
add_action( 'save_post', 'update_event_date_range' );

So, all of this working, i see all of my event posts have the correct string of dates inserted into the new field.

I create a new date range based on first of the month and end of the month because i only want to see the events that occur in May or June or July. And if an event span multiple months then it should show up in those months too.

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

I setup my custom query:

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

I've tried sending the "value" as an array and also as a string. I know I can send an array in the "value", but what I don't know is if the "field" I am comparing my array too can have multiple values in it as well?

What am I missing here OR will this just not work?

I do see that if there is a one day event, meaning in the "auto_event_date_span" field if there is only one date like 20140116 it will be able to match those types of events, but once i add a range of dates to the field, it kinda stops working. bummer.

Question is can i send an array or string and compare using IN in a meta_query?

Sorry if my posts are getting long winded, and ultimately, once done, I'll post a final well documented answer to this otherwise difficult question.

The Codex lists all of the comparison options on the WP_Query page.

If you want events with a start date BETWEEN a range of dates, OR events with an end date BETWEEN a range of dates, something like this should work -

'relation' => 'OR',
    array(
        'key' => 'event_start_date',
        'value' => array( $beginning_of_range, $end_of_range ),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    ),
    array(
        'key' => 'event_end_date',
        'value' => array( $beginning_of_range, $end_of_range ),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    )
  • Hi Milo - Problem with this is if start date is 4/1/2014 and end range is 4/30/2014 then an event that starts on 4/1/2014 and ends on 5/31/2014 won't be listed on the April page. I am filtering the page by month and year. – Robbiegod Jun 9 '14 at 21:31
  • sure it will, you're missing the 'relation' => 'OR' part that makes this work. also note this accounts for events which start before the range, but ends within them, which your solution seems to have overlooked? – Milo Jun 9 '14 at 21:38
  • ok, Milo, I'm going to give this shot. I hope it is this simple. – Robbiegod Jun 10 '14 at 0:23
  • thank milo! I know i tried that before, but i must not have had the 'relation' => 'OR', in there. But this appears to work great, thanks! – Robbiegod Jun 10 '14 at 0:37
  • ooh actually, that didnt work completely. I have an event that is marked start date May 30 2014 to end date August 29 2014. Problem is this event doesn't show up when i am on the July 2014 page. Do you think this is a bug with ACF Date Picker field? – Robbiegod Jun 10 '14 at 1:55
up vote 0 down vote accepted

100% of the credit goes to "keesiemeijer" over at wordpress.org for coming up with a working solution.

Here is the answer in case others are struggling with this. Here is the scenario. You have events that have a start and end date using Advanced Custom Fields Date Picker field type. You are trying to query the events that occur by month and some of your events span multiple months.

In order to be able to do this, you need to drop this function in your functions.php file:

function get_meta_sql_date( $pieces, $queries ) {
    global $wpdb;

    // get start and end date from query
    foreach ( $queries as $q ) {

        if ( !isset( $q['key'] ) ) {
            return $pieces;
        }

        if ( 'event_start_date' === $q['key'] ) {
            $start_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
        if ( 'event_end_date' === $q['key'] ) {
            $end_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
    }

    if ( ( '' === $start_date ) || ( '' === $end_date ) ) {
        return $pieces;
    }

    $query = "";

    // after start date AND before end date
    $_query = " AND (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s) )
        AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) <= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND after end end date
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND (before end date AND end date after start date)
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'event_end_date'
            AND ( CAST(mt1.meta_value AS DATE) <= %s )
            AND ( CAST(mt1.meta_value AS DATE) >= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date, $start_date );

    // OR after end date AND (after start date AND start date before end date) )
    $_query = "OR (
        ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s ) )
        AND ( $wpdb->postmeta.meta_key = 'event_start_date'
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s )
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $end_date, $start_date, $end_date );

    $pieces['where'] = $query;

    return $pieces;
}

Then in your page template add this to your query:

<?php
add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
$all_events = array (
    'post_type' => 'events',
    'posts_per_page' => 50,

    // creates the meta sql join and where clauses
    // which will be filtered in functions.php
    // must be two meta_query arrays
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'       => 'event_start_date',
            'compare'   => '>=',
            'value'     => '20140601',
            'type'      => 'DATE'
        ),
        array(
            'key'       => 'event_end_date',
            'compare'   => '<=',
            'value'     => '20140630',
            'type'      => 'DATE'
        )
    ),
);

$date_query = new WP_Query( $all_events );
remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
?>

After testing it out in my theme, the events that would span from May and end in July will show up in May, June, and July.

Thanks again keesiemeijer!

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.