Forum Discussion

Claudius's avatar
Claudius
Boss
10 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
    10 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

DougS's avatar
DougS
Khoros Oracle
10 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

  • Claudius's avatar
    Claudius
    Boss
    10 years ago

    Thanks. That worked. I should've read the Message resource documentation more thoroughly which mentions "We also support dates in Long format representing the milliseconds since 1970-01-01T00:00:00.000." :smileyembarrassed:

  • bhupen's avatar
    bhupen
    Advisor
    10 years ago

    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) />