Advanced Custom Fields and date picker, show posts only if the day is today no matter the year

Question

I've created a site made just with custom fields about famous people in sport.

In the post page I've a born date and a death date field using the data picker.

What I'm trying to achieve is to show posts of people who have died today in the past, if someone has died the 14th of february 1982 I want to show in a page/widget every 14th of february of this year and the next to come.

I'd also would like to retrieve from the born and death date only the year.

Let's take Jesse Owens. He was born on September 12, 1913 and died March 31, 1980.

I would like to have a post like this: Jesse Owens (1913 – 1980)

• Jesse Owens is the title of the post but how to show 1913 and 1980? Thanks!

Answer 1

It's a little complicated, but we can do this by directly modifying the query with a filter on posts_where.

We'll start with a query for posts with a meta value of today's date, this is assuming a date format of yyyy-mm-dd:

$date_today = date('Y-m-d');
$args = array(
    'posts_per_page' => -1,
    'meta_query' => array(
        array(
            'key' => 'died_date',
            'value' => $date_today,
            'compare' => '=',
            'type' => 'DATE'
        )
    )
);

$results = new WP_Query( $args );

If we look at the WHERE clause of this query, we'll see the relevant bit:

AND ( (wp_postmeta.meta_key = 'died_date'
AND CAST(wp_postmeta.meta_value AS DATE) = '2013-02-14') )

To get this to work, we want to modify the last line to use MySQL's DAYOFYEAR in the comparison.

So here's our filter:

function wpa86916_filter_where( $where ){
    global $wpdb;
    $date_today = date('Y-m-d');
    $parts = explode( 'AND CAST', $where );
    $new_where = " AND DAYOFYEAR( CAST( $wpdb->postmeta.meta_value AS DATE ) ) = DAYOFYEAR( '$date_today' ) ) )";
    $where = $parts[0] . $new_where;
    remove_filter( 'posts_where', __FUNCTION__ );
    return $where;
}

If we back up to our original query, we can now add this filter before the query, and we should get all posts with matching day of year, regardless of year:

add_filter( 'posts_where', 'wpa86916_filter_where' );
$results = new WP_Query( $args );

As for your question regarding extraction of the year from a date, we can use PHP's date and strtotime to do any date extraction and conversion:

$date = '2013-02-14';
echo date( 'Y', strtotime( $date ) );
// prints '2013'