Query between dates using Date Picker fields

• robbiegod

• June 7, 2014 at 1:41 am

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. Natually, it’s possible that an event may span over multiple months.

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 april and ends in may, i want that event to also show up in the month of may because that event is still going on.

As far as code goes, I’ve pieces together this category, but I’m not sure how i should handle the end date.

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

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

	$current_month = $_GET['_m'];
	$current_day = date('d'); // the actual day
	$current_year = $_GET['_y'];
	
	$get_last_day = $current_year.$current_month.$current_day;
	$lastday = date("t", strtotime($get_last_day));
	
	$startday = $current_year.$current_month."01";
	$endday = $current_year.$current_month.$lastday;
	
	echo $startday;
	echo $endday;
	
}
 
$all_events = array (
    'post_type' => 'events',
		'posts_per_page' => -1,
		'status' => 'published',
		'meta_key'		=> 'event_start_date',
		'orderby'		=> 'meta_value_num',
		'order'			=> 'ASC',
		'meta_query'		=> array(
		'relation' => 'OR',
		array(
			'key' => 'event_start_date',
			'value' => array( $startday, $endday ),
			'type' => 'numeric',
			'compare' => 'BETWEEN'
		),
		array(
			'key' => 'event_end_date',
			'value' => array( $startday, $endday ),
			'type' => 'numeric',
			'compare' => 'BETWEEN'
		)
	),
);

• robbiegod

• June 17, 2014 at 6:24 am

$all_events = array (
    'post_type' => 'events',
		'posts_per_page' => 50,
		'status' => 'published',
		'meta_query' => array(
		'relation' => 'OR',
    array(
        'key' => 'event_start_date',
        'value' => array( $startday, $endday ),
        'compare' => 'BETWEEN',
        'type' => 'DATE'
    )
 ),
);

I’ve tried this but it doesnt exactly work.

I saw in your documentation there is a way to query posts between dates. So i tried that, but it doesnt quite seem to work with what i want to do with it.

I have some events and i made a start event date and end event date fields. I want to query the events by the start and end dates.

The problem i have is lets say an event starts in May and ends in July. I have a page that lists the events by month. A page for May, June, and July. So this event that has a start date of May 1 2014 and an end date of July 28, 2014 should also show on the June page.

Do you have any options for getting the query to work in this way? It doesnt seem to be working.

• svsdnb

• June 19, 2014 at 12:13 am

Have you come up with a solution yet. I’m working on the exact same issue with no luck as of yet.

Thank you much!

• robbiegod

• June 19, 2014 at 2:38 am

I have not come up with a solution to this problem. Instead I just went back to the event plugin we were using before as that did what I need it to do. I only had a limited amount of time to work on it.

I would love to know the answer to this problem for future reference, so if someone has a solution, please post it here.

• robbiegod

• June 19, 2014 at 2:46 am

You can try this… I had the question posted in multiple places.

https://wordpress.org/support/topic/query-custom-post-types-by-two-custom-date-fields?replies=11#post-5701103

I wont be able to test this until tonight, but maybe it will help you get started. Let us know if it works.

• svsdnb

• June 19, 2014 at 6:42 am

Were you able to change from the YYYYMMDD saved format to YYYY-MM-DD or is that why you added $current_month, $current_day, $current_year?

• svsdnb

• June 19, 2014 at 7:21 am

I was able to solve my problem by converting YYMMDD (Ymd) to unix timestamp in my functions.php then you can query array for a date range.

• robbiegod

• June 19, 2014 at 7:45 am

In my custom field, I am using yymmdd (all lowercase) – I see it stored in the database like 20140615, for example.

I added current month, current day, current year variables because I have a widget on my website that shows events that occur in a single month.

Do you mind posting your code and let me know what settings you used in the custom field too?

• svsdnb

• June 19, 2014 at 11:44 pm

What I did was convert the stored data for my end date to unix timecode in my functions.php file like so

function custom_unixtimesamp ( $post_id ) {
    if ( get_post_type( $post_id ) == 'events' ) {
	$enddate = get_post_meta($post_id, 'event_date_ends', true);

		if($enddate) {
			$dateparts = explode('/', $enddate);
			$newdate1 = strtotime(date('d.m.Y H:i:s', strtotime($dateparts[1].'/'.$dateparts[0].'/'.$dateparts[2])));
			update_post_meta($post_id, 'unixstartdate', $newdate1  );
		}
	}
}
add_action( 'save_post', 'custom_unixtimesamp', 100, 2);

then queried as such

$today = time();	

	$args = array(
        'post_type' => 'events',
	'post_status' => 'publish',
	'posts_per_page' => '10',
	'meta_query' => array(
		array(
			'key' => 'unixstartdate',
			'compare' => '>=',
			'value' => $today,
			)
			),
	'meta_key' => 'event_date_begins',
	'orderby' => 'meta_value',
	'order' => 'ASC',

However what I thought fixed the issue actually didn’t. While you’re trying for a monthly date range I’m looking for just upcoming events. I had 2 events created. One future date and one past date. Using this code the past date event went away so I thought problem solved until I went in to enter more future dates which did not show up. I removed the functions.php code and the result was the same so it must not be converting the time properly. I too am using yymmdd but sql, to my understanding need yy-mm-dd (2014-06-19).

Are you using WordPress? If so how did you get it to understand the year without the hyphens?

For your array couldn’t you just query event_start_date $current_month?

'key' => 'event_start_date',
'value' => $current_month

then array

'key' => 'event_end_date',
'value' => $current_month

using a compare of AND

That should return anything with a start date for the month of current and end date for the current month.

• svsdnb

• June 20, 2014 at 12:19 am

OMG what a pain. I got it!

so use their tutorial on comparing dates but instead of date(‘Ymd’) use current_time

$today = current_time('Ymd');
 
$args = array (
    'post_type' => 'post',
    'meta_query' => array(
		array(
	        'key'		=> 'start_date',
	        'compare'	=> '<=',
	        'value'		=> $today,
	    ),
	     array(
	        'key'		=> 'end_date',
	        'compare'	=> '>=',
	        'value'		=> $today,
	    )
    ),
);

I removed all that stuff in the functions.php and just did

$event1 = current_time(‘Ymd’) then for my array I used a key of my end date field compare to greater than $event1 (aka current date in Ymd).

• robbiegod

• June 20, 2014 at 1:24 am

I don’t see any difference between date(‘Ymd’) and current_time(‘Ymd’); They both output todays date.

It seems to do what I wanted this to do was even more involved. The tricky part was when an event spanned multiple months. Say an Event would start in May and end in July. The event would not show up in June because it was out of the scope of the range of the event.

So, a very smart fellow by the name of “keesiemeijer” over at wordpress.org helped me get the query correct.

Here is the solution:

Add this to your functions.php file – modify as needed:

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 add this to your page where you do 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 );
?>

So, holly molly was this involved. I would recommend that they add a similar solution to the documentation on the ACF website.

This worked for me.

• svsdnb

• June 20, 2014 at 1:34 am

I know SQL uses date() but I thought WordPress uses the_date and current_time. In either event for my installation current_time worked vs date.

But yeah… that’s quite a process. So do you have to change those date ranges every month? There has to be an easier way to get those date ranges than a manual range like that.

• robbiegod

• June 20, 2014 at 4:43 am

No manual month changes. I can handle that easily with some date variable manipulation.

On my site i have a widget that uses AJAX to send the month and year to the page. I grab those values and make some date variables.

Mainly i look at today and get the month and year. I make a variable for the first of the month like this: 20140601 — i store that value in a variable. Then i also get the last day of the given month and make my ending day variable with that value. I use php “t” value to get the last day of the month.

That code ends up looking like this:

$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));

In my final code, i pass the $startday and $endday to the wordpress query in each of those spots where in the example I have the date hardcoded.

'value' => $startday,

• chirayu.emphatic

• April 19, 2016 at 10:05 pm

$query_args = array (
‘post_type’ => $post_types,
‘post_status’ => ‘publish’,
‘no_found_rows’ => 1,
‘post__not_in’ => explode(‘,’, $params[‘exclude’]),
‘order’ => ‘ASC’,
‘meta_query’ => array(
‘relation’ => ‘OR’,
array(
‘key’ => ‘date_of_birth’,
‘compare’ => ‘=’,
‘value’ => $today,
),
array(
‘key’ => ‘date_of_death’,
‘compare’ => ‘=’,
‘value’ => $today,
)
),
);

Can u help me Current date match with either date of birth or either date of death.
Above query not working no one post display.
I need either date of birth or Date of death Date match with current date and display all posts. I m near by.

• fuberator

• January 26, 2018 at 2:38 am

@svsdnb brilliant copy-paste-reload-works 10 second fix for me

muchas gracias!