Forum Discussion

Claudius's avatar
Claudius
Boss
11 years ago

Getting correct date format for use in LiQL?

I'd like to get all messages from my community that haven't seen a reply more than 24h ago using Community API v2 LiQL via something like SELECT subject, board.title, view_href FROM messages WHERE ...
  • DougS's avatar
    DougS
    11 years ago

    Hi Claudius,

     

    I think we should probably add a method that will return you a date as an iso8601 string to the datesupport freemarker object.  Until we have that though, LiQL also lets you represent dates as long values (milliseconds since the epoch), so you could re-write your LiQL query to this and it should work this same:

     

    SELECT subject, board.title, view_href FROM messages WHERE depth= 0 AND replies.count(*) = 0 AND post_time > 1411776000000 AND post_time < 1414368000000 ORDER BY post_time

     You could use the datesupport.millisecondsAsString method to write out your dates and that should hopefully get it to work for you.

     

    -Doug

bhupen's avatar
bhupen
Advisor

Hi DougS 

 can you please explain me what is that mean <1411776000000 .

 

AND post_time > 1411776000000 AND post_time < 1414368000000 ORDER BY post_time

 

I just want to show the post for today only. what will ne the right digit for that i.e.  post_time > 1411776000000 AND post_time < 1414368000000

 

Thanks

 

OlivierS's avatar
OlivierS
Lithium Alumni (Retired)
10 years ago

bhupen 

 

This is the EPOCH time in milliseconds.

You can use this converter to get the date in a more readable way ...

  • bhupen's avatar
    bhupen
    Advisor
    10 years ago

    OlivierS  Can you please help me in this:

     

    I want to show post who have most reply in the day. I got the most reply post but I am not sure how do i show post who have most reply in a day

    <#assign messages = rest("2.0","/search?q=" + "SELECT * FROM messages WHERE replies.count(*)>0 AND depth=0 order by replies.count(*) desc LIMIT 5"?url) />

    • OlivierS's avatar
      OlivierS
      Lithium Alumni (Retired)
      10 years ago

      bhupen 

       

      I'm not familiar with Freemarker dates.

      What you could do, is get the current date and convert it to epoch: ${.now?date?long}

      Figure out how many milliseconds are in 24 hours. Substract the value to the previous one (${.now?date?long}). so you will have a time range.

       

      It won't be the most replies in a day, but the most replies in the last 24 hours.