XF 2.2 How many posts in a given timeframe for a given user?

[Mendalla]

I have been going over our statistics for the past three years. It's not pretty. But one thing that is nagging at me is how much impact one poster is happening. He posts reams of posts mostly in barely readable gibberish. So I want to separate out his posts to see how big a percentage he accounts for. To be honest, it might as much as half in 2021 given how low our posting rate has dropped.

But I am not sure how to do this. Been fiddling with SQL queries (I use Transact-SQL all the time at work but don't have much experience with MySQL/MariaDB) but I wonder if someone knows another way. Maybe an add-on that enhances the statistics? I know his total posts for the history of the board, but I want to see year over year what is his impact.

 

[Paul B]

This will give all posts from user ID 1 for 2021.

SELECT * FROM `xf_post` WHERE user_id = 1 and post_date BETWEEN 1609477200 and 1641013199

Change the user ID as required.

The dates are in UNIX timestamp: https://www.unixtimestamp.com/index.php

If you just want the count without the posts, use:

SELECT COUNT(post_id) FROM xf_post WHERE user_id = 1 and post_date BETWEEN 1609477200 and 1641013199

 

[Mendalla]

This will give all posts from user ID 1 for 2021.

SELECT * FROM `xf_post` WHERE user_id = 1 and post_date BETWEEN 1609477200 and 1641013199

Change the user ID as required.

The dates are in UNIX timestamp: https://www.unixtimestamp.com/index.php

If you just want the count without the posts, use:

SELECT COUNT(post_id) FROM xf_post WHERE user_id = 1 and post_date BETWEEN 1609477200 and 1641013199

Okay, it is the date stamps that had me stymied and I had not got around to consulting the Xenforo manuals to see if there was anything there on what format you used. Merci.

 

[briansol]

Unix Time Stamp - Epoch Converter

Epoch and unix timestamp converter for developers. Date and time function syntax reference for various programming languages.

www.unixtimestamp.com

 

[Mendalla]

Unix Time Stamp - Epoch Converter

Epoch and unix timestamp converter for developers. Date and time function syntax reference for various programming languages.

www.unixtimestamp.com

Nice. I might get some mileage out of that.

 

[Paul B]

The dates are in UNIX timestamp: https://www.unixtimestamp.com/index.php

cough

 

[Mendalla]

cough

Crap, didn't even notice it was the same URL. Okay, thanks to you, then.

 

[Sim]

At this time of the year, I like to generate a whole bunch of stats for my sites from the previous year which I then turn into nice charts and tables and publish on my site for my users (although it's mostly for my benefit - helps me identify outliers and troublemakers!)

Here is a variation on a query that I use to generate a count of posts made each day - I copy the output into a spreadsheet, calculate a 28 day moving average (so I'll typically set the start date to 28 days before Jan 1st), and then generate a pretty chart showing average post counts per day throughout the year.

It is easily modified to show data for a single user - and you can use the @year variable at the top to adjust which year to show - run it multiple times to get multiple data sets that you can add to your spreadsheet / chart.

Things to adjust:

1. set the timezone for FROM_UNIXTIME functions to your local TZ
2. set the @year
3. set the userid of the user (by default it shows data for all users)
4. if you have a spam folder or other folders you might want to exclude, add them to the thread.node_id exclusion list of ignored nodes
5. uncomment the message state filter if you want to exclude deleted posts - I don't because a deleted post is still a post that a user made (and my spam posts get moved to a separate node - hence the previous exclusions)

/*
  Posts per day

  Ignored nodes:

  - 50: spam
 
*/

SET TIME_ZONE = 'Australia/Sydney'; # time zone for FROM_UNIXTIME functions
SET @year = 2021;

SELECT
  DATE(FROM_UNIXTIME(post.post_date)) AS postdate,
  COUNT(post_id) AS postcount

FROM `xf_post` AS post
  LEFT JOIN xf_thread AS thread
    ON (post.thread_id = thread.thread_id)

WHERE TRUE

  #AND post.user_id = 1 # uncomment this and change the user id if you want to show data for a specific user

  #AND thread.node_id NOT IN (50) # uncomment this and add any nodes you want to ignore
  #AND message_state = 'visible'  # uncomment this if you want to exclude deleted posts

  AND DATE(FROM_UNIXTIME(post.post_date)) >= CONCAT_WS('-', @year, 1, 1)
  AND DATE(FROM_UNIXTIME(post.post_date)) <= CONCAT_WS('-', @year, 12, 31)

GROUP BY postdate
ORDER BY postdate ASC

I like to use FROM_UNIXTIME because it is far easier to work with real MySQL dates eg "2022-01-03" ... than unix timestamps!