CODEWITHSUNDEEP
phpwordpress
Ismael Moral
Ismael Moral

Reputation: 732

Get posts between dates

I need to extract all posts inside wordpress which has been published between the last 6 months and today.

I'm using WP_Query to do this, 

    $today      = new \DateTime();
    $prev       = new \DateTime();
    $interval   = new \DateInterval('P6M');

    $prev->sub( $interval );

    $args = [
        'post_type'     => 'post',
        'date_query'    => [
            [
                'before'    => $today->format('Y-m-d H:i:s'),
                'after'     => $prev->format('Y-m-d H:i:s'),
                'inclusive' => true
            ]
        ]
    ];
echo '[POST] Today: ' . $today->format('Y-m-d') . "\n";
echo "[POST] Count:" . $wpQuery->post_count . "\n";
echo '[POST] Start date: ' . $prev->format('Y-m-d') . "\n";

Output

Today: 2017-03-09
Count: 7
Start date: 2016-09-09

If I do a select in wordpress database, within the same to dates I get 2086 rows.

SELECT COUNT(ID)
    -> FROM wp_posts
    -> WHERE DATE(post_date) >= '2016-09-09' AND DATE(post_date) < '2017-03-09' 
    ->    AND post_type = 'post';

SQL output

+-----------+
| count(ID) |
+-----------+
|      2086 |
+-----------+

What am I doing wrong with WP_Query? Why did I get two different results?

Thanks.

Upvotes: 2

Views: 2867

Answers (2)

MahdiY
MahdiY

Reputation: 1306

  1. In your sql query you set post date smaller than 2017-03-09 DATE(post_date) < '2017-03-09' but in WP_Query with 'inclusive' => true is Less than or equal to 2017-03-09 DATE(post_date) <= '2017-03-09'.

  2. You can print sql query generated by WP_Query like this:

    echo $wpQuery->request;

  3. Try it:

    SELECT COUNT(ID) FROM wp_posts WHERE wp_posts.post_date >= '2016-09-09 15:19:39' AND wp_posts.post_date <= '2017-03-09 16:19:39' AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish'

Upvotes: 2

Naincy
Naincy

Reputation: 2943

SELECT COUNT(ID)
FROM wp_posts
WHERE DATE(post_date) >= '2016-09-09' AND DATE(post_date) < '2017-09-09' 
   AND post_type = 'post';

Upvotes: 0

Related Questions