Forum Discussion

VarunGrazitti's avatar
VarunGrazitti
Boss
9 years ago

Get the posts for specified time range in V1 or V2

As per this reply from NicoB,  we need to find the posts in the community created within the last 1 week. Instead of the board, I am trying this call for category, but it is not working, though it i...
irach15's avatar
irach15
Maven

hi DougS,

if I want to get the latest posts/or count  for the last week, not specifying a week, 

Is it any way to get the results?

I did try, not working:
SELECT count(*) FROM messages WHERE board.id = 'topics' AND post_time < 604800 ORDER BY post_time DESC

 

Any suggestions?

DougS's avatar
DougS
Khoros Oracle
9 years ago

You would have to specify a timerange in the query (you need a start time and an end time). If you were coding this in an endpoint, you could generate the query you need with something like this:

 

<#assign now = dateSupport.now />
<#assign nowMs = now.millisecondsAsString /> 
<#assign weekBefore = now.addWeek(-1) />
<#assign weekBeforeMs = weekBefore.millisecondsAsString />
<#assign query = "SELECT count(*) FROM messages WHERE board.id = 'technology' AND post_time > " + weekBeforeMs + " AND post_time < " + nowMs + " ORDER BY post_time DESC" />

-Doug