wordpress Wp_query and meta query issue with date field

Question

I have Projects inserted as posts in my WordPress database. currently on my home, the last 3 published project is displayed. now my purpose is that I want first display the project which is expiring today than the last published project.

for example, there are 2 projects are expiring today than on the home page it will display 2 projects which are expiring today and 1 project which published last. it means a total of 3 projects will display.

please check below WP_query which returns last published project only

$args = array('post_type' => 'ignition_product', 'posts_per_page' => $project_count, 'paged' => $paged);

$newargs = apply_filters('project_query', $args);
$wp_query = new WP_Query($newargs);

the below query I try using meta key & value but no luck. "ign_fund_end" is stored a date as a string so I think that's why not comparing date. my final goal is I described as above total 3 projects should display. first should be today expiring then after last published.

$args = array(
        'post_type' => 'ignition_product',
        'posts_per_page' => $project_count,
        'paged' => $paged,        
        'meta_query' => array(// WordPress has all the results, now, return only the events after today's date
            array(
                'key' => 'ign_fund_end', // Check the start date field
                'value' => date('m/d/Y'), // Set today's date (note the similar format)
                'compare' => '>=', // Return the ones greater than today's date
                'type' => 'DATE' // Let WordPress know we're working with date
            )
    ));

please check the below image for reference.

any solution appreciated.

Can you share an example value for `ign_fund_end` field? and are you using acf for creating the custom field? — Saqib Amin, Apr 01 '19 at 10:49
@SaqibAmin i have added one image for the value reference. please check. thanks — Code Embassy, Apr 01 '19 at 10:53
Thanks for posting the screenshot. You will need to change the save format for this field to `Y-m-d` format to work correctly. — Saqib Amin, Apr 01 '19 at 10:55
@SaqibAmin is it not possible to chnage str to date at time of we fire the query ? — Code Embassy, Apr 01 '19 at 11:00
That conversion won't be possible. You can change the save format of the meta key easily, can you mention the plugin you are using for creating the custom field? — Saqib Amin, Apr 01 '19 at 11:02
@SaqibAminnot sure because it is already in the form. if i provide you teamviewer you can check ? — Code Embassy, Apr 01 '19 at 11:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191018/discussion-between-saqib-amin-and-code-embassy). — Saqib Amin, Apr 01 '19 at 11:05

score 0 · Answer 1 · answered Apr 01 '19 at 11:13

You just need to remove type from the array parameters.

$args = array(
        'post_type' => 'ignition_product',
        'posts_per_page' => $project_count,
        'paged' => $paged,        
        'meta_query' => array(// WordPress has all the results, now, return only the events after today's date
            array(
                'key' => 'ign_fund_end', // Check the start date field
                'value' => date('m/d/Y'), // Set today's date (note the similar format)
                'compare' => '>=', // Return the ones greater than today's date
                'type' => 'DATE' // Let WordPress know we're working with date
            )
    ));

To:

$args = array(
        'post_type' => 'ignition_product',
        'posts_per_page' => $project_count,
        'paged' => $paged,        
        'meta_query' => array(// WordPress has all the results, now, return only the events after today's date
            array(
                'key' => 'ign_fund_end', // Check the start date field
                'value' => date('m/d/Y'), // Set today's date (note the similar format)
                'compare' => '>=', // Return the ones greater than today's date
                //'type' => 'DATE' // Let WordPress know we're working with date
            )
    ));

Note: The reason is that in table meta_value is not DATE type.

In the PHPMyAdmin, the default date type is:

2019-04-16

yes but. i want to compare two dates then will it compare both dates using >= — Code Embassy, Apr 01 '19 at 11:14
But `meta_value` field is not `DATE` type that's why it's not working — Gufran Hasan, Apr 01 '19 at 11:15
@CodeEmbassy, Please see this solution. https://wordpress.stackexchange.com/a/50187/137328 — Gufran Hasan, Apr 01 '19 at 11:19

score 0 · Accepted Answer · answered Apr 01 '19 at 12:28

Since your custom field ign_fund_end is not in MySQL date compatible format so that is the main reason for your WP_Query to not work in the expected way. My recommendation is to save a timestamp of end date in a custom field using save_post and then change the $args for WP_Query to work on that field.

Here is complete solution for your issue:

1: Save Timestampe in a Custom field

add_action( 'save_post', function( $post_id ) {
    if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) return;
    if ( $parent_id = wp_is_post_revision( $post_id ) ) {
        $post_id = $parent_id;
    }

    if( isset( $_POST['ign_fund_end'] ) && !empty($_POST['ign_fund_end']) ) {
        $end_date = $_POST['ign_fund_end'];
        $end_date = strtotime($end_date);
        update_post_meta( $post_id, '__end_date', $end_date );
    }
} );

2: Modify the $args

$args = array(
    'post_type' => 'ignition_product',
    'posts_per_page' => $project_count,
    'orderby'   => 'meta_value_num',
    'order'     => 'ASC',
    'meta_key'  => '__end_date',
    'paged' => $paged,        
    'meta_query' => array(// WordPress has all the results, now, return only the events after today's date
    array(
        'key' => '__end_date', // Check the start date field
        'value' => strtotime('today'), // Set today's timestamp
        'compare' => '>=', // Return the ones greater than today's date
        'type' => 'NUMERIC'
    )
));

wordpress Wp_query and meta query issue with date field

2 Answers2