• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL query for post count in a forum during date range

#1
Does anybody have a SQL query handy that would allow you to calculate the number of posts that occurred in a specific forum during a given timeframe (such as last year for example)? Thanks in advance!
 

Jim Boy

Well-known member
#2
Off the top of my head, it would be something like:

select count(*) from xf_post p, xf_thread t where p.thread_id=t.thread_id and t.node_id=[id of the forum in question] and p.post_date between unix_timestamp([start date/time]) and unix_timestamp([end date/time])
 

TheBigK

Well-known member
#4
Well, how do we go about fetching records from 'Last Month'? So if it's October, I want records from September. If it's January, I want records from December.

For the sake of simplicity, let's just consider table with two columns:

apples_sold | Date
33 | 3 September 2015
54 | 2 October 2015
211 | 14 September 2015
.
.
.
 

Mouth

Well-known member
#5
how do we go about fetching records from 'Last Month'?
Code:
select count(*)
from xf_post p, xf_thread t
where p.thread_id=t.thread_id and
t.node_id=[id of the forum in question] and
p.post_date between DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
 

TheBigK

Well-known member
#6
Thanks @Mouth . I tried that in my query that tries to fetch the count of articles and username shared by our 'bloggers' in XenPorta. It's not working. Would really appreciate your inputs -

Code:
SELECT user.username, COUNT(articles.thread_id) AS article_countFROM xf_user AS user
INNER JOIN xf_user_profile AS user_profile
ON (user.user_id = user_profile.user_id)
INNER JOIN ewrporta2_catlinks AS catlinks
ON (catlinks.user_id = user.user_id)
INNER JOIN ewrporta2_articles AS articles
ON (articles.thread_id = catlinks.thread_id)
WHERE MONTH(CURDATE() articles.article_date between DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
GROUP BY user.user_id
 

Mouth

Well-known member
#7
It's not working.
Ahh, forgot that XF stores dates as integers and not datetime. Change to;

Code:
[...]
where articles.article_date between UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'));
 
Last edited:

TheBigK

Well-known member
#8
Ahh, forgot that XF stores dates as integers and not datetime. Change to;

Code:
[...]
where articles.article_date between UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'));
Tried that and XenForo shows following error -

ErrorException: Fatal Error: syntax error, unexpected '%' - library/Report/Model/Index.php:18
Generated By: Unknown Account, A moment ago

My code:
PHP:
public function getAuthorWithPostCount()
{
return $this->_getDb()->fetchPairs('
SELECT user.username, COUNT(articles.thread_id) AS article_countFROM xf_user AS user
INNER JOIN xf_user_profile as user_profile
ON (user.user_id = user_profile.user_id)
INNER JOIN EWRporta2_catlinks AS catlinks
ON (catlinks.user_id = user.user_id)
INNER JOIN EWRporta2_articles AS articles
ON (articles.thread_id = catlinks.thread_id)
WHERE articles.article_date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'))
GROUP BY user.user_id
');
}
 

Mouth

Well-known member
#9
The single quote mark to define the date format is closing out your SQL inside fetchpairs() before it's fully represented. You'll need to 'escape'(backslash) these single quotes. Try;
Code:
WHERE articles.article_date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, \'%Y-%m-01 00:00:00\')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), \'%Y-%m-%d 23:59:59\'))