find the difference between two date/time values

[krishnakishoreg]

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

[kristian_kolb]

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 

read more here;

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions
http://en.wikipedia.org/wiki/Unix_epoch
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

/K

[nagarajsf]

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,

[aszewczyk]

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.

[kristian_kolb]

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

[krishnakishoreg]

Thanks a lot, by doing some change query worked.