4 Replies Latest reply on Jun 30, 2017 11:05 AM by mesverrum

    How to filter timstamps in sql.

    shaun_9999

      Good morning/afternoon.

       

      I am not the best at SQL, but I am trying to resolve an alert that isn't being triggered.  I opened the database and I need to filter the dates that alerts have been triggerd.

      I thought it would have been similar to a lookup function.

      eg - timestamp > xx/xx/xxxx, < xx/xx/xxxx.

       

      Again, I haven't really touched SQL.  So my knowledge of a query is all but nothing. so I am trying to learn how it functions and how to generate commands on the back end.

       

       

       

      I found a few and tried them (I suppose I should generate then again and show you the error messages)

       

       

       

      date {

        match => { "DATETIME" => [ "MMM d HH:mm:ss", "MMM dd HH:mm:ss", "ISO8601" ] }

        target => "@timestamp"

        add_tag => [ "tmatch" ]

      }

       

       

      SELECT *
       
      FROM MyTable
       
      WHERE [dateColumn] > '3/1/2009' AND [dateColumn] <= DATEADD(day,'3/31/2009',1)
                      
      --make it inclusive for a datetime type
         
      AND DATEPART(hh,[dateColumn]) >= 6 AND DATEPART(hh,[dateColumn]) <= 22
                      
      -- gets the hour of the day from the datetime
         
      AND DATEPART(dw,[dateColumn]) >= 3 AND DATEPART(dw,[dateColumn]) <= 5
                      
      -- gets the day of the week from the datetime

       

       

      select DATEPART(hh, Calltime)as MyHour, DATEPART(dd, Calltime)as MyDay, DATEPART(yy, calltime)as MyYear into Mytemp

      from deployment

      order by myyear, MyDay, MyHour

      select *,COUNT(myhour)as MyCount from mytemp

      group by myyear,myday,myhour

      order by MyCount

      Drop mytemp

       

       

      If we take the last example - would I write.

      where [timestamp] > '25/06/2017' AND [timestamp] <=dateadd (day, 30/06/2017', 1)

       

      Msg 8116, Level 16, State 1, Line 1

      Argument data type varchar is invalid for argument 2 of dateadd function.

      Msg 8116, Level 16, State 1, Line 1

      Argument data type varchar is invalid for argument 2 of dateadd function.

        • Re: How to filter timstamps in sql.
          mesverrum

          So if I read this correctly you are looking for events in the last 7 days?  Or do you need to be more granular like between specific hours on those days?  The way you are doing it seems unnecessarily complex for someone starting out in SQL.

           

          SQL is complaining because it doesn't recognize the string you fed it is supposed to be a datetime format so it can't do relative math operations against it, getting the format right for this can be a pain because it varies based on how your regional settings are and the way dates are displayed on screen can seem kind of misleading.  You can try this and it may work:

          where [timestamp] > cast('20170625' as date) AND [timestamp] <=cast('20170631' as date)

           

          The way i normally do a lookup for recent dates would be like this

           

          where datediff(day,[timestamp],getdate())< 7

           

          That way I don't have to fuss with figuring out the time formatting and such

            • Re: How to filter timstamps in sql.
              shaun_9999

              TBH I haven't touched any real language since my college/uni days and that was PHP/C++ and other languages that my job never uses.  But that was about a decade ago.  So my best attempts at trying to even recall how to write a script and MS KB articles was the best I had.  Ergo, why you thought it was so massively complex and I thought it was perfectly fine.

              And it's all about expanding my knowledge and if I ever get issues like this, I want to at least attempt to resolve my issues and not raise a support call.

            • Re: How to filter timstamps in sql.
              yaquaholic

              First off all, the date format from your output example appears to be dd/mm/yyyy, where as your SQL code is using US style mm/dd/yyyy.

              That might be a local thing, but worth checking.

               

              Here is an working example (UTC time format)

               

              SELECT * FROM AlertHistory

              WHERE TimeStamp > '2017-06-02'                            -- this assumes '2017-06-02 00:00:00' or 00:00 2nd of June

                AND TimeStamp < '2017-06-03 20:00:00'                -- specifying eight o'clock on the 3rd of June 2017

               

              I hope it helps