Select event where start date is smaller than current date and end date is greater than current date

Select event where start date is smaller than current date and end date is greater than current date

Suppose an event A start date is 2013-06-12 and end date is 2013-07-30. I’ve a query where start date is 2013-06-20 and end date is 2013-06-30. So Event A should show in my query result. but I don’t get it. This is my query

Read More
$query = "
SELECT DISTINCT ID
FROM {$wpdb->prefix}posts
JOIN {$wpdb->prefix}postmeta m1
ON ( {$wpdb->prefix}posts.ID = m1.post_id )
WHERE
{$wpdb->prefix}posts.post_type = 'tribe_events'

AND {$wpdb->prefix}posts.post_status = 'publish'

AND (

    ( (m1.meta_key = '_EventStartDate' AND m1.meta_value >= '$start_date') AND ( m1.meta_key = '_EventStartDate' AND m1.meta_value <= '$end_date' ))

    OR 
    ( (m1.meta_key = '_EventEndDate' AND m1.meta_value >= '$start_date') AND ( m1.meta_key = '_EventEndDate' AND m1.meta_value <= '$end_date' ) )

    OR
    ( (m1.meta_key = '_EventStartDate' AND m1.meta_value < '$start_date') AND ( m1.meta_key = '_EventEndDate' AND m1.meta_value > '$end_date') )

    )

ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";

$result = $wpdb->get_results($query);

Thanks in advance.

Related posts

1 comment

  1. You can try to replace this kind of comparison:

    AND m1.meta_value >= '$start_date'
    

    with:

    AND CAST(m1.meta_value AS DATE) >= '" . date( "Y-m-d H:i:s", $start_date ) . "'
    

    or if you would use Unix timestamp for the meta values then you could try:

    AND m1.meta_value+0  >= ". strtotime( $start_date ) ."
    

    Update:

    Here is a modification of your query:

    $query = " SELECT DISTINCT * FROM {$wpdb->posts}
    INNER JOIN {$wpdb->postmeta} m1 ON ( {$wpdb->posts}.ID = m1.post_id )
    INNER JOIN {$wpdb->postmeta} m2 ON ( {$wpdb->posts}.ID = m2.post_id )
    WHERE {$wpdb->posts}.post_type = 'tribe_events'
    AND {$wpdb->posts}.post_status = 'publish'
    AND (
           ( ( m1.meta_key = '_EventStartDate' AND CAST( m1.meta_value AS DATE ) >= %s ) AND ( m2.meta_key = '_EventStartDate' AND CAST( m2.meta_value AS DATE ) <= %s ))
           OR 
           ( ( m1.meta_key = '_EventEndDate' AND CAST( m1.meta_value AS DATE ) >= %s ) AND ( m2.meta_key = '_EventEndDate' AND CAST( m2.meta_value AS DATE ) <= %s ) )
           OR
           ( ( m1.meta_key = '_EventStartDate' AND CAST( m1.meta_value AS DATE ) < %s ) AND ( m2.meta_key = '_EventEndDate' AND CAST( m2.meta_value AS DATE ) > %s ) )
        )
    ORDER BY {$wpdb->posts}.post_date DESC;";
    
    $query = $wpdb->prepare( $query, $start_date, $end_date, $start_date, $end_date, $start_date, $end_date );
    $result = $wpdb->get_results( $query );
    

    where the two INNER JOINS are added since you are filtering with AND on two different meta keys.

Comments are closed.