1

Original post: Query between dates using Date Picker fields

I originally made a post last year on this same subject, but seems the update to WP v4.2.1 has broken my filter. In summary, I am using ACF and date picker plugin in an Events custom post type. I am trying to query events between a start and end date. I also have events that can span multiple months. For example, an event may start in april but end in june. I want that event to display in april, may, and june.

I had this working using a filter when on v3.9 of wordpress. I'm trying to get wordpress updated on all of my websites, but this filter breaks after updating to v4.2.1. This broke after updating WP 4.2.1 and also after updating ACF to latest version of 4. I have since updated to ACF PRO v5.2.3

Does anyone know of a fix?

Here is the code that worked great when on WP v3.9.

My Event Widge Template:

<?php
/* Template Name: Events Widget */
$today = date('Ymd');

if (isset($_GET['_m'])) {

    $current_month = str_pad($_GET['_m'], 2, '0', STR_PAD_LEFT);
    $current_day = "01"; // day one
    $current_year = $_GET['_y'];

    $get_last_day = $current_year.$current_month.$current_day;
    $lastday = date("t", strtotime($get_last_day));

    $tempstartday = $current_year.$current_month.$current_day;
    $tempendday = $current_year.$current_month.$lastday;

    $startday = date('Ymd', strtotime($tempstartday));
    $endday = date('Ymd', strtotime($tempendday));

} else {

    $current_month = str_pad(date('m'), 2, '0', STR_PAD_LEFT);
    $current_day = "01"; // day one
    $current_year = date('Y');

    $get_last_day = $current_year.$current_month.$current_day;
    $lastday = date("t", strtotime($get_last_day));

    $tempstartday = $current_year.$current_month.$current_day;
    $tempendday = $current_year.$current_month.$lastday;

    $startday = date('Ymd', strtotime($tempstartday));
    $endday = date('Ymd', strtotime($tempendday));
}

add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

$qryevents = array(
    'post_type' => 'events',
    'posts_per_page' => 50,
    'status' => 'published',
    'meta_key' => 'event_start_date',
    'orderby' => 'meta_value',
    'order' => 'ASC',

    // produces meta join and where clauses for the query
    // which will be filtered in functions.php
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'     => 'event_start_date',
            'compare' => '>=',
            'value'   => $startday,
            'type' => 'DATE'
        ),
        array(
            'key'     => 'event_end_date',
            'compare' => '<=',
            'value'   => $endday,
            'type' => 'DATE'
        )
    )
);

$loop = new WP_Query( $qryevents );
remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

if ( $loop->have_posts() ) :
while ( $loop->have_posts() ) : $loop->the_post();

// Let's format the dates
$get_start_date = get_field('event_start_date');
$get_end_date = get_field('event_end_date');
$event_start_date = DateTime::createFromFormat('Ymd', $get_start_date);
$event_end_date = DateTime::createFromFormat('Ymd', $get_end_date);
// End of date definitions

// Let's get the event start and end times
$get_start_time = get_field('event_start_time');
$get_end_time = get_field('event_end_time');
// end of times

// Let's get the times of the events now
$specify_event_time = "";

$show_event_times = get_field('specify_event_times');
if($show_event_times){
        foreach($show_event_times as $specify_event_time){
            // Do nothing; this puts the yes value into the varible for us to later on the page.
            // echo $specify_event_time;
        }
}
// End of the specify times


$event_month_spans = get_field('event_month_span');
?>

<div class="<?php echo (++$j % 2 == 0) ? 'full row' : 'full row alt'; ?>">
<p><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></p>
    <?php if($specify_event_time == "yes"): ?>
    <p class="event-date"><a href="<?php the_permalink(); ?>"><?php echo $event_start_date->format('M d, Y'); ?> <?php echo $get_start_time; ?> - <?php echo $event_end_date->format('M d, Y'); ?> <?php echo $get_end_time; ?></a></p>
  <?php else: ?>
    <p class="event-date"><a href="<?php the_permalink(); ?>"><?php echo $event_start_date->format('M d, Y'); ?> - <?php echo $event_end_date->format('M d, Y'); ?></a></p>
  <?php endif; ?>
</div>   

<?php
endwhile;

else:
?>
<p>No scheduled events.</p>
<?php endif; wp_reset_query(); ?>

In my functions.php file I have this function:

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 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;
}
  • what does "breaks" mean exactly? how does it break? do you have debugging enabled? have you inspected the final query to see what it looks like and why it might be failing? – Milo May 1 '15 at 16:45
  • hi milo..by break I mean that the query no longer shows the events that span april to june if I am looking at may events like it used too. It only shows those events that have a start date in may and an end date in may too. – Robbiegod May 2 '15 at 4:17
3

There's no need for the custom meta SQL filter - the beauty of storing dates in the format Ymd is that you can treat them numerically, and MySQL will still be able to find events in a given "range" and sort them ascending/descending.

I've done this recently on another site using ACF for start/end date:

if ( ! empty( $_GET['_y'] ) )
    $year = absint( $_GET['_y'] );
else
    $year = date( 'Y ');

if ( ! empty( $_GET['_m'] ) && in_array( $month = absint( $_GET['_m'] ), range( 1, 12 ) ) )
    $month = zeroise( $month, 2 );
else
    $month = date( 'm' );

$qryevents = array(
    'post_type'      => 'events',
    'posts_per_page' => 50,
    'post_status'    => 'publish',
    'orderby'        => 'meta_value_num', // Ensure order is numerically based
    'order'          => 'ASC',

    'meta_query'     => array(
        'relation' => 'AND',
        array(
            'key'     => 'event_start_date',
            'compare' => '>=',
            'value'   => "{$year}{$month}01",
            'type'    => 'NUMERIC',
        ),
        array(
            'key'     => 'event_end_date',
            'compare' => '<=',
            'value'   => "{$year}{$month}31", // Doesn't matter if there aren't 31 days in this month, will still work,
            'type'    => 'NUMERIC',
        )
    )
);

No need for the overly complex date string calculations, and no need for the filter.

  • Hi DeadMedia, so the query above will show an event that is in May if my start date is in april and my end date is in june? I did actually find a solution to my problem, which i will post here soon. – Robbiegod May 5 '15 at 19:29
  • @Robbiegod Theoretically yes, but my code will only ever query a range of one month - as did your original code. – TheDeadMedic May 6 '15 at 9:20
0

The Date & time utilities utilize a detachment of date & time patterns. The date () utility revisits the date in this pattern: ‘YYYY-MM-DD’.

SQL Date functions oracle

  • Have you read the question and description properly? – Karun Jul 6 '15 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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