Forum Discussion

tmarshall's avatar
tmarshall
Advisor
8 years ago

Dynamically get date in LIQL call through API

Hi, 

 

I've read the following:

https://community.lithium.com/t5/Developers-Discussion/Getting-correct-date-format-for-use-in-LiQL/m-p/180275#M7166
https://community.lithium.com/t5/Developers-Discussion/Is-it-possible-to-show-only-current-day-posts/m-p/188365#M7710

 

I've also seen the documentation, where is gives an example of putting in a hard coded timestamp. Which is great.

https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collection=messages#constraints

SELECT subject, id FROM messages WHERE post_time > 2013-10-07T10:04:30-08:00 AND post_time < 2013-11-07T10:04:30-08:00

What I'm trying to so is programically get the time in the API call that I've giving to to our webteam to pull the last 30 days of solved posts from a specific category. I manually put in EPOC as placeholders and it works great, and successful return.

 

https://community.lithium.com/api/2.0/search?q=SELECT+conversation.solved%2C+conversation.view_href%2C+conversation.messages_count%2C+author.login%2C+author.view_href%2C+subject%2C+%20metrics.views%2C+search_snippet%2C+post_time%2C+conversation.last_post_time%2C+c_interests+FROM+messages+WHERE+depth%3D0+AND+post_time+%3E+1430070533+AND+post_time+%3C+1493146187+AND+conversation.solved%3Dtrue+AND+category.id%3D%22lithium%22+ORDER+BY+conversation.messages_count+DESC+LIMIT+3&xslt%3Djson.xsl

I'm not sure how to programically put in a greater than '30 days ago' and less than 'now'. (~30 days is about 2505600 in epoc) I can do it in freemarker with 'assign' variables in the community when making a component, but didn't know if there was a clever API way to make the date dynamic in the call itself?   Below is effectively what I would like to pass? 

https://community.lithium.com/api/2.0/search?q=SELECT+conversation.solved%2C+conversation.view_href%2C+conversation.messages_count%2C+author.login%2C+author.view_href%2C+subject%2C+%20metrics.views%2C+search_snippet%2C+post_time%2C+conversation.last_post_time%2C+c_interests+FROM+messages+WHERE+depth%3D0+AND+post_time+%3E+${now?date?long}-2505600+AND+post_time+%3C+${now?date?long}+AND+conversation.solved%3Dtrue+AND+category.id%3D%22lithium%22+ORDER+BY+conversation.messages_count+DESC+LIMIT+3&xslt%3Djson.xsl

Is this possible outside of freemarker in a native LIQL API query, or should I just make the webteam programically pass in EPOC on their side into my API call? 

 

Cheers, 

Tim

  • PerBonomi's avatar
    PerBonomi
    8 years ago

    Just a suggestion; I like to plan ahead and be ready for change. Hard code sucks in the future.

     

    With something like this you leave yourself a choice:

    • Automatically query back x days, or
    • Add a variable to the request url for a specific date, or
    • Add a variable to the request url for a specific number of days to go back
    <#assign date_before_set = http.request.parameters.name.get("date_before", "")?string /> <!-- use the url to determine what date you want to check back to -->
<#assign days_back_set = http.request.parameters.name.get("days_back", "")?string /> <!-- use the url to determine how many days back to look at -->

<#assign date_today_for_query = .now?datetime?string["yyyy-MM-dd"]/>

<#if date_before_set == ""> <!-- if you didn't pass a hard date in the url then continue -->
	<#if days_back_set == ""> <!-- if you didn't pass a specific number of days then check variable from code, e.g. 30 days -->
		<#assign days_back = 30/>
	<#else>
		<#assign days_back = days_back_set?number/> <#-- make sure the number passed in the url isn't seen as string; this can bork the query -->
	</#if>
	<#assign date_today_long = .now?long/>
	<#assign date_before_long = date_today_long - ( days_back * 1000 *60 *60 *24 ) />
	<#assign date_before = date_before_long?number_to_datetime?string["yyyy-MM-dd"]/>
</#if>
Test:
Today: ${date_today_for_query}<br/>
Before: ${date_before}

    Now you can use this in your query (post_time as example):

    ... WHERE post_time >= ${date_before}T00:00:00.000 AND post_time <= ${date_today_for_query}T00:00:00.000 ...

     

    If you call just the url you will go back 30 days from today.

    But if you add url?date_before_set=2016-12-30 you can go back to any date you want without much hassle.

    And, if you add url?days_back_set=31 you can go back any specific number of days you want without much hassle.

     

    Like I said, it can pay to plan ahead and not have to alter much code, but it's completely optional.

4 Replies

Sort By
  • VarunGrazitti's avatar
    VarunGrazitti
    Boss
    8 years ago

    tmarshall - AFAIK, there is no such way of doing this in the LIQL, you have to make this one in the custom components using FTL. I did this once about a couple of years ago, still works fine :)

  • PerBonomi's avatar
    PerBonomi
    Boss
    8 years ago

    Just a suggestion; I like to plan ahead and be ready for change. Hard code sucks in the future.

     

    With something like this you leave yourself a choice:

    • Automatically query back x days, or
    • Add a variable to the request url for a specific date, or
    • Add a variable to the request url for a specific number of days to go back
    <#assign date_before_set = http.request.parameters.name.get("date_before", "")?string /> <!-- use the url to determine what date you want to check back to -->
<#assign days_back_set = http.request.parameters.name.get("days_back", "")?string /> <!-- use the url to determine how many days back to look at -->

<#assign date_today_for_query = .now?datetime?string["yyyy-MM-dd"]/>

<#if date_before_set == ""> <!-- if you didn't pass a hard date in the url then continue -->
	<#if days_back_set == ""> <!-- if you didn't pass a specific number of days then check variable from code, e.g. 30 days -->
		<#assign days_back = 30/>
	<#else>
		<#assign days_back = days_back_set?number/> <#-- make sure the number passed in the url isn't seen as string; this can bork the query -->
	</#if>
	<#assign date_today_long = .now?long/>
	<#assign date_before_long = date_today_long - ( days_back * 1000 *60 *60 *24 ) />
	<#assign date_before = date_before_long?number_to_datetime?string["yyyy-MM-dd"]/>
</#if>
Test:
Today: ${date_today_for_query}<br/>
Before: ${date_before}

    Now you can use this in your query (post_time as example):

    ... WHERE post_time >= ${date_before}T00:00:00.000 AND post_time <= ${date_today_for_query}T00:00:00.000 ...

     

    If you call just the url you will go back 30 days from today.

    But if you add url?date_before_set=2016-12-30 you can go back to any date you want without much hassle.

    And, if you add url?days_back_set=31 you can go back any specific number of days you want without much hassle.

     

    Like I said, it can pay to plan ahead and not have to alter much code, but it's completely optional.

  • tmarshall's avatar
    tmarshall
    Advisor
    8 years ago

    Hey PerBonomi Thanks for the reply. This is very clever :smileytongue: and could / can use this in a component, but it does not appear that I put a formula in the API LIQL query, but that the webteam has to do the clever bit you mention on the webpage to pass in the dates. Appreciate the reponse and will mark as solution as it is a very good way to use freemarker in a component or wrapper. 

  • PerBonomi's avatar
    PerBonomi
    Boss
    8 years ago

    Hi again, thanks for the solve :)

     

    You could create an endpoint with that code. Then you could make it output xml, csv or json that the web team can parse, just from calling one url.