4 Replies Latest reply on May 12, 2015 11:57 AM by augusto

    SWQL query with date > comparison always execute as if >=

    augusto

      I have the following query

       

      SELECT  EventID, EventTime, Message, NetObjectID, NetObjectType, NetworkNode FROM Orion.Events WHERE  Acknowledged=0 AND EventTime > '07/05/2015 15:28:49' ORDER BY EventTime DESC

       

      which for some reason, will always return the results as if I were performing the query with

       

      SELECT  EventID, EventTime, Message, NetObjectID, NetObjectType, NetworkNode FROM Orion.Events WHERE  Acknowledged=0 AND EventTime >= '07/05/2015 15:28:49' ORDER BY EventTime DESC

       

      Is this a bug or am I doing something wrong here?

        • Re: SWQL query with date > comparison always execute as if >=
          Craig Norborg

          Hazarding a guess here, the "EventTime" is actually stored down to the millisecond I believe.  I'm guessing that your comparison time, since it doesn't specify milliseconds, is rounded off to 0 milliseconds (ie: '07/05/2015 15:28:49.000'), but the actual data has values greater than ".000" in the milliseconds, so the comparison is working just as it should be.  In SQL you would display this similar to this:

           

          SELECT  TOP 100 EventID, EventTime, Message, NetObjectID, NetObjectType, NetworkNode, CONVERT(char(50), EventTime, 13) AS Milli

          FROM Events

          WHERE  EventTime >= '07/05/2015 15:28:49'

          ORDER BY EventTime DESC

           

          Or in SWQL there is a "Millisecond" function, so you could probably do this:

          SELECT  EventID, EventTime, Millisecond(EventTime) AS Milli, Message, NetObjectID, NetObjectType, NetworkNode FROM Orion.Events WHERE  Acknowledged=0 AND EventTime >= '07/05/2015 15:28:49' ORDER BY EventTime DESC

           

          To see what you're really comparing with? 

          1 of 1 people found this helpful
            • Re: SWQL query with date > comparison always execute as if >=
              augusto

              Thanks Craig, it seems this is the correct path, but I still found something weird....

               

              When I run:

              SELECT  EventID, EventTime, Millisecond(EventTime) AS Millis, Message, NetObjectID, NetObjectType, NetworkNode FROM Orion.Events WHERE  Acknowledged=0 AND EventTime > '07/05/2015 15:28:49.258' AND EventTime < '07/05/2015 15:28:50' ORDER BY EventTime DESC

               

              I get EventTime = 07/05/2015 15:28:49 and Millis = 260

               

              If I try running

              SELECT  EventID, EventTime, Millisecond(EventTime) AS Millis, Message, NetObjectID, NetObjectType, NetworkNode FROM Orion.Events WHERE  Acknowledged=0 AND EventTime > '07/05/2015 15:28:49.259' AND EventTime < '07/05/2015 15:28:50' ORDER BY EventTime DESC


              it returns nothing. I am confused - should the Millisecond function returns the milliseconds part of a DateTime? If that is the case and the time for that event is "07/05/2015 15:28:49.260", why am I not getting a result when I search using "07/05/2015 15:28:49.259" ?

                • Re: SWQL query with date > comparison always execute as if >=
                  Craig Norborg

                  Hmm.  Curious, let me prefix this by saying that I really have no idea.    The millisecond thing I was fairly certain of, but this is just conjecture...

                   

                  I found an article that says that datetime stamps are stored with a limited precision, they said 0.00333 seconds, and that "Values are rounded to increments of .000, .003, or .007 seconds".  There is a link below to an article that supports this.  I'm guessing that you're running into some kind of rounding error on the calculations your doing, perhaps its rounding off your constant that your comparing your eventtime to.  I'm guessing the actual eventtime's are actually rounded already before they are stored.

                   

                  datetime and smalldatetime