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 depth= 0 AND replies.count(*) = 0 AND post_time > 2014-09-27T00:00:00-00:00 AND post_time < 2014-10-27T00:00:00-00:00 ORDER BY post_time

(Hint: for post_time you need to specify upper boundary as well because otherwise results get too vague.)

I'm facing some challenges building the right timestamp format for my query using the datesupport Freemarker object:

Using 

${datesupport.addHour(-24).dateAsString}T${datesupport.addHour(-24).timeAsString}:00-00:00

 I come as close as "09-11-2014T15:07:00-00:00" which is almost perfect, but the date portion is wrong as it should be 

"yyyy-MM-dd"

Unfortunately .dateAsString does not take any parameters to format the date output. Also any of my attempts to apply a ?string("...") formatting just gave me a "lithium.eval.velocity.DateDisplayTemplateModel@72a600e4"

 

Any suggestions on how I can pull a LiQL friendly date frome the datesupport freemarker object?

  • 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

6 Replies

Sort By
  • DougS's avatar
    DougS
    Khoros Oracle
    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

  • Claudius's avatar
    Claudius
    Boss
    11 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) />

  • 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.