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