This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

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?

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

  • 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" ?

  • 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

  • Thanks Craig that seems to be the case really.

    Unfortunately, it also means that it's a matter of "trial and error" to figure out the exact timestamp to use when querying for alerts / events in SWQL, which ultimately means that I'll have to keep track of the event id in order to avoid duplicates. Either that or round it up to the next 10th millisecond.