topic Re: find the difference between two date/time values in Splunk Dev

find the difference between two date/time values

krishnakishoreg — Mon, 28 Sep 2020 15:28:03 GMT

Hi,

I need small help to build a query to find the difference between two date/time values of a log in table format. For example in_time=2013-12-11T22:58:50.797 and out_time=2013-12-11T22:58:51.023.

tried this query but i didn't get the result.
| eval otime=out_time| eval itime=in_time | eval TimeDiff=otime-itime | table out_time in_time TimeDiff

Re: find the difference between two date/time values

kristian_kolb — Thu, 12 Dec 2013 10:47:21 GMT

You cannot compare times that way. You'll need to convert them to epoch first. If one of those timestamps are already being used as the timestamp for the event, then the conversion has already been made for that timestamp, and it is availible in the _time field. Otherwise you'll need to do the following;

your_base_search 
| eval it = strptime(in_time, "%Y-%m-%dT%H:%M:%S.%3N") 
| eval ot = strptime(out_time, "%Y-%m-%dT%H:%M:%S.%3N") 
| eval diff = tostring((ot - it), "duration") 
| table in_time, out_time, diff

Re: find the difference between two date/time values

krishnakishoreg — Thu, 12 Dec 2013 16:56:39 GMT

Thanks a lot, by doing some change query worked.

Re: find the difference between two date/time values

kristian_kolb — Thu, 12 Dec 2013 19:30:27 GMT

feel free to mark the question as answered a/o upvote if it solved your problem. Thanks, K

Re: find the difference between two date/time values

aszewczyk — Wed, 10 Aug 2016 20:45:47 GMT

In case anyone was scratching their head like me, the time formats should be:

"%Y-%m-%d %H:%M:%S.%3N"

There should be spaces where the 'T's are.

Re: find the difference between two date/time values

nagarajsf — Mon, 28 Oct 2019 23:22:53 GMT

Hi,
I'm in the same scenario, but trying to get the difference from CREATED_DATE and current timestamp. For that, it is not working.

    base_search  
| eval it = strptime(CREATED_DATE, "%Y-%m-%d %H:%M:%S") 
| eval nowstring=strptime(now(), "%Y-%m-%d %H:%M:%S") 
| eval ticket_duration=tostring((now() - it), "duration" )  
| table DESCRIPTION,CREATED_DATE,TICKET_STATUS,UPDATE_DATE, ticket_duration

base_search  
| convert timeformat='%Y-%m-%dT%H:%M:%S' mktime(CREATED_DATE) mktime(now()  AS  _now) 
| eval duration=(_now-CREATED_DATE)/86400 
|table TTID,MANAGER_NAME,SEVERITY,DESCRIPTION,CREATED_DATE,TICKET_STATUS,UPDATE_DATE, duration

In both ways I'm getting null value, ticket_duration=null
Can you please suggest any?

Thanks,