fctester123456
8 years agoAdept
forum average post count per hour
Hi, i have the following question: 1. how to get average post count per hour? 2. how to get count of posts in the last 24 hour? The 'count' mentioned above refer to number of 'messages' crea...
- 8 years ago
1. You can use the below query to get the post count of last 24 hours.
SELECT count(*) messages WHERE post_time > 2017-10-07T10:04:30-08:00
<#assign today = .now?long /> <#assign minus7days= today - (7 * 24 * 60 * 60 * 1000) /> <#assign since7days= minus7days?number_to_datetime?iso('GMT-08:00') /> <#assign postCount = "SELECT count(*) FROM messages WHERE post_time > ${since7days}" />
Here is the documentation https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collection=messages#constraints-post_time
Here is the similar thread which might be helpful https://community.lithium.com/t5/Developer-Discussion/posts-count-of-last-7-days/m-p/315826#M12204
2. For the average count, it depends for how long period you want to count the average. Example for one week.
With the above query, you will get the post count of one week which you can divide by hours in one week.
e.g
An average of one week.
<#assign postCount = 700 /> <#assign average =( postCount / 1*24*7)?floor />