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!
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