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

SQL queries to find out your forum daily stats

Andy.N

Well-known member
#1
In the absence of a builtin XF stats system, I'd like to monitor the activities on my site.
Number of new registration, posts, thread per day.

There should be some queries that can be used to get these info out. Then I can do a pre/post Xenforo to see if the new platform improves activity level.
Thanks
 

Jake Bunce

XenForo moderator
Staff member
#6
Number of new registration, posts, thread per day.
I like queries.

For registrations per day (from this post):

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m-%d') AS date
FROM xf_user
GROUP BY date
ORDER BY date
For posts per day:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
FROM xf_post
GROUP BY date
ORDER BY date
For threads per day:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m-%d') AS date
FROM xf_thread
GROUP BY date
ORDER BY date
These all generate results like this:

Screen shot 2010-12-27 at 9.17.08 AM.png
 

SchmitzIT

Well-known member
#7
I started working on a mod that basically would show similar stats as a few of Paul's vB mods do (Today's visiting members, today's registrations, etc) a few weeks ago, but have to admit I've gotten sidetracked with other projects. I have the AdminCP part ready, and mostly need to tie together the templates and queries with the settings.

I'll see if I can somehow quickly whip something together for this. It might not be complete, or perfect, but it might serve as a start, so I can finetune it later on.
 

Jake Bunce

XenForo moderator
Staff member
#9
Now, how we aggregate them by week/month?
By month:

For registrations per month:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%m') AS date
FROM xf_user
GROUP BY date
ORDER BY date
For posts per month:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%m') AS date
FROM xf_post
GROUP BY date
ORDER BY date
For threads per month:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%m') AS date
FROM xf_thread
GROUP BY date
ORDER BY date
 

Jake Bunce

XenForo moderator
Staff member
#10
Now, how we aggregate them by week/month?
By week:

For registrations per week:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%U') AS week, MIN(FROM_UNIXTIME(register_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_user
GROUP BY week
ORDER BY week
For posts per week:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%U') AS week, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_post
GROUP BY week
ORDER BY week
For threads per week:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%U') AS week, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_thread
GROUP BY week
ORDER BY week
These per week queries return slightly different results than the others. You will see the week number of the year, like this:

Screen shot 2011-03-15 at 10.02.19 AM.png

And startOfWeek isn't necessarily the first day of the week. Rather it's the earliest day of the week within the data set. So, for example, if no one posted on Sunday then startOfWeek will show the date for Monday.
 

Bob

Well-known member
#11
Note: posted this for reference :)

DATE_FORMAT and TIME_FORMAT Output Formats for FROM_UNIXTIME function

format Parameter Output Format
%r 12-hour time (hh:mm:ss (AM|PM))
%T 24-hour time (hh:mm:ss)
%Y Numeric year, 4 digits
%y Numeric year, 2 digits
%m Month with leading 0 (01, 02-12)
%c Month without leading 0 (1, 2-12)
%M Month name (January, February, and so on)
%b Month name, abbreviated (Jan, Feb, and so on)
%D Day of the month with an English suffix (1st, 2nd, and so on)
%d Day of the month with leading 0 (00, 01, 02-31)
%e Day of the month without leading 0 (0, 1, 2-31)
%W Weekday name (Sunday, Monday, and so on)
%a Weekday name, abbreviated (Sun, Mon, and so on)
%H Hour (00, 01-23)
%k Hour (0, 1-23)
%h Hour (01, 02-12)
%I Hour (01, 02-12)
%l Hour (1, 2-12)
%i Minutes (00, 01-59)
%S Seconds (00, 01-59)
%s Seconds (00, 01-59)
%P AM or PM
%U Week number in the year,in which Sunday is the first day of the week
$u Week number in the year,in which Monday is the first day of the week
%X & %V Year and week number, respectively,in which Sunday is the first day of the week
%x & %v Year and week number, respectively,in which Monday is the first day of the week
%j Day of year with leading 0's (001, 002-366)
%w Weekday number (0=Sunday, 1=Monday, and so on)
%% Literal %
 

Jake Bunce

XenForo moderator
Staff member
#13
Oh, my mistake.

By week:

For registrations per week:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(register_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_user
GROUP BY yearWeek
ORDER BY yearWeek
For posts per week:

Code:
SELECT COUNT(*) AS posts, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_post
GROUP BY yearWeek
ORDER BY yearWeek
For threads per week:

Code:
SELECT COUNT(*) AS threads, FROM_UNIXTIME(post_date, '%Y-%U') AS yearWeek, MIN(FROM_UNIXTIME(post_date, '%Y-%m-%d')) AS startOfWeek
FROM xf_thread
GROUP BY yearWeek
ORDER BY yearWeek
 

Jake Bunce

XenForo moderator
Staff member
#15
You can add an offset where the date fields appear in the query. For example, for registrations per day:

Code:
SELECT COUNT(*) AS registrations, FROM_UNIXTIME(register_date - 8*60*60, '%Y-%m-%d') AS date
FROM xf_user
GROUP BY date
ORDER BY date
That would subtract 8 hours.