Forum Discussion

Natkinson's avatar
Natkinson
Genius
4 years ago

Can someone explain how to filter by last_publish_time

I am trying to write a custom component that involves filtering by last_publish_time (we're trying to grab posts from the past 7 days). But I'm having a difficult time understanding what number to use for last_publish_time.

In this LiQL doc https://developer.khoros.com/khoroscommunitydevdocs/docs/messages#last_publish_time-1 they use seemingly arbitrary and huge numbers that I can't make sense of. But in this doc https://developer.khoros.com/khoroscommunitydevdocs/docs/get-messages-posted-or-edited-in-a-specified-range-of-time it references using "n days" when using this in a filter.

Can anyone explain how to calculate the number to use? Ideally we'd like something like 

SELECT * FROM messages WHERE last_publish_time <= 7 days

Obviously that doesn't work, just an example of what I'm trying to figure out 😉

Thanks!

  • luk's avatar
    luk
    6 months ago

    The docs for the last_publish_date constraint can be found here: https://developer.khoros.com/khoroscommunitydevdocs/docs/messages#last_publish_time-1

    Basically you have to use a unix epoch timestamp in milliseconds which is inconsistent with other datetime constraints which require you to use an ISO-date (the same that is returned by the API if you query such a field...), for example post_time... it doesn't make any sense, but it is what it is...

    The FreeMarker part would look something like this (untested):

     

    <#assign now = datesupport.now />
<#assign lastWeek = now.addWeek(-1).millisecondsAsString />
<#assign query = "SELECT * FROM messages WHERE last_publish_time >= ${lastWeek} ORDER BY last_publish_time DESC LIMIT 10" />
<#assign response = liql(query) />

<#list (response.data.items)![] as obj>
    <#-- do your thing here with each message object -->
</#list>

     

    For more info about the custom Khoros datesupport FreeMarker context object see the docs here: https://developer.khoros.com/khoroscommunitydevdocs/reference/datesupport-1