XF 2.0 SQL query to get posts from today only

[Mave]

Currently I use

SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id = 6

To get all posts with prefix_id 6.

How do i get all posts from prefix_id 6 AND posted today?

I tried this but it's not working:

$sql = 'SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id = 6 AND B.post_date = DATE(NOW())';

 

[MySiteGuy]

That query is only going to retrieve posts made the same exact time the query is made.

$sql = 'SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id = 6 AND B.post_date = (NOW() - INTERVAL 24 HOUR)';

 

[Mave]

That query is only going to retrieve posts made the same exact time the query is made.

$sql = 'SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id = 6 AND B.post_date = (NOW() - INTERVAL 24 HOUR)';

Thank you for answering @MySiteGuy
Unfortunately the code is not working. I'm not getting any errors, but the result is 0, although posts should show up. Any idea why?

This results in 38000

$sql = 'SELECT * FROM xf_thread A';

This should result in 3 but results in 0:

$sql = 'SELECT * FROM xf_thread A WHERE A.post_date = DATE(NOW() - INTERVAL 24 HOUR)';

 

[Mouth]

How do i get all posts from prefix_id 6 AND posted today?

SELECT * FROM xf_post P INNER JOIN xf_thread T ON P.thread_id = T.thread_id WHERE T.prefix_id = 6 AND P.post_date >= UNIX_TIMESTAMP(CURDATE());

 

[Mave]

SELECT * FROM xf_post P INNER JOIN xf_thread T ON P.thread_id = T.thread_id WHERE T.prefix_id = 6 AND P.post_date >= UNIX_TIMESTAMP(CURDATE());

Hey @Mouth unfortunately also this code is not working. (still getting 0) Any idea why?
I'm 100% positive that I should get results as I'm using:

SELECT * FROM xf_thread A INNER JOIN xf_post B ON A.post_date = B.post_date WHERE prefix_id = 7 AND P.post_date >= UNIX_TIMESTAMP(CURDATE());

And I did in fact posted topics with that prefix already today.

 

[Mouth]

Any idea why?

I'm on XF1.5, and it works fine. Unless there's a difference in XF2 db schema, I don't see why. Try remove the prefix_id inside the WHERE condition and make sure you get results.

And I did in fact posted topics with that prefix already today.

That code should fail, because P.post_date is invalid since you're using A and B for table naming.
There's also no need to JOIN the post table, since you're not using it anyway. Your A.post_date = B.post_date condition ensure's it will only look at the first post in the thread, since only the first post and the thread creation will have identical date and times.

 

[Mave]

I'm on XF1.5, and it works fine. Unless there's a difference in XF2 db schema, I don't see why. Try remove the prefix_id inside the WHERE condition and make sure you get results.

That code should fail, because P.post_date is invalid since you're using A and B for table naming.
There's also no need to JOIN the post table, since you're not using it anyway. Your A.post_date = B.post_date condition ensure's it will only look at the first post in the thread, since only the first post and the thread creation will have identical date and times.

@Mouth My bad, but still even with using A it won't work.

$sql = 'SELECT * FROM xf_thread';

350000 results

$sql = 'SELECT * FROM xf_thread WHERE post_date = DATE(NOW() - INTERVAL 1 DAY)';

0 results, although there are at least 10 topics and posts created today....

 

[Mave]

I have no idea why, but suddenly it works now.
So thank you so much for your time @Mouth your code does work!

$sql = 'SELECT * FROM xf_thread WHERE post_date >= UNIX_TIMESTAMP(CURDATE())';

 

[Mouth]

it works now.

Great

$sql = 'SELECT * FROM xf_thread WHERE post_date = DATE(NOW() - INTERVAL 1 DAY)';

0 results, although there are at least 10 topics and posts created today....

FYI, post_date is an integer utilising Unix time, whereas DATE returns an ISO date format. Thus using an equals(=) comparator will never return any results. That's why you need the UNIX_TIMESTAMP function around any SQL DATE functions, to convert it into a unix time integer for comparison.