SQL query for post count in a forum during date range

[Baron]

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]

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])

 

[Baron]

Thanks so much!!

 

[TheBigK]

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]

how do we go about fetching records from 'Last Month'?

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]

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 -

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]

It's not working.

Ahh, forgot that XF stores dates as integers and not datetime. Change to;

[...]
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'));

 

[TheBigK]

Ahh, forgot that XF stores dates as integers and not datetime. Change to;

[...]
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:

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]

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;

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\'))

 

[LaxmiSathy]

Hello,
Am looking for a similar sql query such that in where conditional I enter the node id for the category say "Official Forums" - https://xenforo.com/community/#official-forums.1

it lists all the nodes under this category and the post count in each of the node during the specific date range.

Thanks.

 

[jankit1981]

We want to find out how many posts and threads were created in a certain set of Nodes between a set time period (let us say YTD). Output would be

for date range A to B

node id = C has posts X
node id = D has posts Y
node id = E has posts X
node id = F has posts Y

etc.

for date range A to B

node id = C has threads X
node id = D has threads Y
node id = E has threads X
node id = F has threads Y

Can we get an SQL query to find this out?

Note: These are 400 private notes else I would have checked manually or via XML sitemap.

Thanks in advance.

 

[duderuud]

Maybe this add-on can help you?

[OzzModz] Simple Stats

Simplify your forum management with an add-on designed to track and display essential statistics! Features: 📋 ACP Stats Page: View a detailed table of key statistics directly in the Admin Control Panel. (Requires "View statistic" ACP...

xenforo.com