2 Replies Latest reply on Apr 10, 2012 4:07 PM by eddiec

    Working with dates in ePO Web  API

      Hi all,

       

      I've written a few web API queries now and I'm getting more comfortable with putting together s-expressions, but one common requirement still baffles me: working with dates. I have seen the examples in the API scripting guide that show how to use a newerThan or between operator, but I don't understand the literal date parameter that is being used in these examples. For example in the API scripting guide we have:

       

      (where (newerThan OrionAuditLog.EndTime 3600000))

       

      In this example what is 3600000? If i divide 3600000 / 60 / 60 i get 1000... Are we returning a record where the endtime is within the last 1000 hours?

      I'm just not quite understanding how that works. How would I select a record newerThan a fixed date? (Using a lazy SQL DateTime format for this example):

       

      (where (olderThan EPOLeafNode.LastUpdate "2012-04-01 00:00:00.000"))

       

      The intent here would be to select records from the EPOLeafNode table where a machine hasn't checked in since Midnight, April 1st. Is there some sort of conversion that takes a standard date and will turn it into a long like in the first example?

       

      I'm equally confused on the usage of the timestamp operator. I understand that it's a unary operator that takes a value and will convert it into a timestamp to be used with between, as in the following example:

       

      (where (between OrionAuditLog.EndTime (timestamp 1288888320000) (timestamp 1288888360000)))

       

      But what exactly is that value? Ticks? I've looked up date / time operators used in Common Lisp; get-universal-time, decode-universar-time, etc but these don't seem to be available as operators in the web API.

       

      Any help anyone could give me, or even just a link to a reference to help me figure it out, would be very much appreciated.

       

      Thanks,
      -Eddie

        • 1. Re: Working with dates in ePO Web  API
          jking

          Both operators olderThan and newerThan take two arguments; both arguments are converted to Longs and summed. The sum is compared against the current ticks count on the local machine. If one of the arguments is given as a String, it must be convertible to a Date from which a ticks value can be computed.

           

           

          The S-Expressions used in the queries aren’t a full LISP implementation, so the LISP-y commands won’t be supported by the client.  (We just use S-Expressions as the intermediate form because it's a great format for manipulation -- we can inject permissions clauses and perform other non-destructive modifications to the intermediate form without having to parse SQL)


          All timestamps (unless documented elsewhere) will be in UTC ticks.

           

           

          Your examples:


           

          (where (newerThan OrionAuditLog.EndTime 3600000))


          This would select records that have ended within the last 1000 hours ( now < EndTime + 3600000 )


           

          (where (olderThan EPOLeafNode.LastUpdate "2012-04-01 00:00:00.000"))


          This would select records that were last ended within the last 370351000 hours (probably not what you intended)


           

          To find the nodes that haven’t been updated since April 1st, you would use the olderThanAbsolute operator which takes two args and returns arg1 < arg2.

           

          The documentation of S-expression operators is definitely something we'd look for feedback on ... if people are really drilling into core.executeQuery and building these queries on the fly, maybe it's worth our investing more in that direction.  The command core.listDatatypes gives basic info about what operators are available, but doesn't really show you how they're used.  I've created more than one query in the UI with some basic values that I could then export to see what expression was created.

           

          Definitely looking to your opinions about where you think we should go here.  

           

          Thanks,

          Jon

          • 2. Re: Working with dates in ePO Web  API

            Jon,

             

            Thank you very much for your informative answer. I read it this morning and I've been excited all day about trying to dive a little bittle deeper and figure out some examples that I can post here to help others. However, my day has been jam-packed trying to solve other problems that keep popping up. I'm going to do some work using your suggestions and get back to you with more detail within a few days.

             

            Thanks,

            Eddie