Forum Discussion

Inactive User's avatar
Inactive User
8 years ago

posts count of last 7 days

Hello everybody!

I'm trying to write a custom component to get the count of threads posted within the last 7 days or so using APIv2. Here's what i did!

<#assign today = .now?long />
<#assign minus7days= today - (1000 * 24 * 60 * 60 * 7) />
<#assign since7days= aWeekBack?string["yyyy-MM-dd"] + "T00:00:00-00:01" />

<#local query = "SELECT count(*) FROM messages WHERE depth = 0 AND post_time > " + since7days />

This doesn't return the right count. Can someone pls help me with the right approach?

 

Best

  • luk's avatar
    luk
    8 years ago

    This should work as well

     

    <#assign now = datesupport.now />
<#assign today = now.millisecondsAsString?number?number_to_datetime?iso("Europe/Zurich") />
<#assign lastWeek = now.addWeek(-1).millisecondsAsString?number?number_to_datetime?iso("Europe/Zurich") />
<#assign query = "SELECT count(*) FROM messages WHERE post_time > " + lastWeek + " AND post_time < " + today + "ORDER BY post_time ASC" />
<#assign data = rest("2.0", query) />

3 Replies

Sort By
  • Payal's avatar
    Payal
    Director
    8 years ago

    Hi Sengo, I have made changes to your custom code, can you please try the following code once?

     

    <#assign today = .now?long />

    <#assign minus7days= today - (7 * 24 * 60 * 60 * 1000) />

    <#assign since7days= minus7days?number_to_datetime?iso('GMT-08:00') />

    <#assign query = "SELECT count(*) FROM messages WHERE depth = 0 AND post_time > ${since7days}" />

     

    LMK if this works for you.

    Thanks!!

  • luk's avatar
    luk
    Boss
    8 years ago

    This should work as well

     

    <#assign now = datesupport.now />
<#assign today = now.millisecondsAsString?number?number_to_datetime?iso("Europe/Zurich") />
<#assign lastWeek = now.addWeek(-1).millisecondsAsString?number?number_to_datetime?iso("Europe/Zurich") />
<#assign query = "SELECT count(*) FROM messages WHERE post_time > " + lastWeek + " AND post_time < " + today + "ORDER BY post_time ASC" />
<#assign data = rest("2.0", query) />
  • Inactive User's avatar
    Inactive User
    8 years ago
    Thanks Payal for your time and help but sorry to tell you this didn't work.