3

I have a metaquery for a custom post type "events". Their start date is stored as a meta field in this format:

2016-02-05 19:00:00

These are my query args:

$args = array(
    "posts_per_page" => 12,
    "paged" => $paged,
    "post_type" => array(
        "event",
        "post"
    ) ,
    "post_status" => "publish",
    "meta_key" => "_thumbnail_id",
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'event_start',
            'value' => date("Y-m-d H:i:s") ,
            'compare' => '>=',
            'type' => 'DATE'
        ) ,
        array(
            'key' => 'event_start',
            'compare' => 'NOT EXISTS'
        )
    ) ,
    array(
        'key' => 'foreign_language',
        'value' => 0
    ) ,
    array(
        'key' => 'hide_from_most_recent',
        'value' => 0
    )
);

The problem: The current date + time is: 2016-02-05 12:29:16

This query should return the post with this meta value: 2016-02-05 19:00:00

Yeterday (one day before this date) it worked. Today it doesn't. Means:If the day is the same but hour and minute are bigger than now, it does not work, as it should. It seems, that the hour, minute and second are beeing ignored. I guess the issue is caused by the fact, that I have mixed up time & date.

Is that correct? How should the query look like, that it works?

Many Thanks

7
  • 19:00:00 is greater than 12:29:16, you're checking if it's equal or less than.
    – Milo
    Feb 5, 2016 at 16:24
  • @Milo Sorry, was a type. I also added some details to the description.
    – user998163
    Feb 5, 2016 at 19:37
  • Append the time to the current date manually, like date( 'Y-m-d' ) . ' 00:00:00'
    – Milo
    Feb 5, 2016 at 20:06
  • @Milo I did that. it did not help. This is the sql query, that runs (reduced) SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'event_start' AND CAST(wp_postmeta.meta_value AS DATE) >= '2016-02-05 01:00:00' ) ) AND wp_posts.post_type = 'event' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 12
    – user998163
    Feb 5, 2016 at 21:16
  • if it's being cast as date, the time is being stripped and should be irrelevant.
    – Milo
    Feb 5, 2016 at 21:19

1 Answer 1

3

Found the error:

$args = array(
        "posts_per_page" => 12,
        "paged" => $paged,
        "post_type" => array(
            "event",
            "post"
        ) ,
        "post_status" => "publish",
        "meta_key" => "_thumbnail_id",
        'meta_query' => array(
            'relation' => 'OR',
            array(
                'key' => 'event_start',
                'value' => date("Y-m-d H:i:s") ,
                'compare' => '>=',
                'type' => 'DATETIME'
            ) ,
            array(
                'key' => 'event_start',
                'compare' => 'NOT EXISTS'
            )
        ) ,
        array(
            'key' => 'foreign_language',
            'value' => 0
        ) ,
        array(
            'key' => 'hide_from_most_recent',
            'value' => 0
        )
    );

It must be DATETIME instead of DATE as comparing type.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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