How can I find the time duration between two fields?

[locose]

Trying to find the time duration between 2 fields

Field name : START_TS
2017-08-16 04:07:00.0

Field name : END_TS
2017-08-16 04:12:00.0

I tried something like....

my search query  | eval Starttime=strftime(START_TS,"%y-%m-%d %H:%M:%S:%N")
 | eval Endtime=strftime(END_TS,"%y-%m-%d %H:%M:%S:%N")
 |eval duration = Endtime - Starttime

But it didn't work.

[myriadic]

I found the problem(s):

1. "Year" has to be a capital "Y", instead of lowercase.

2. Before "%N", you have a colon, instead of a period.

Here's my working search:

| makeresults | eval START_TS="2017-08-16 04:07:00.0" | eval END_TS="2017-08-16 04:12:00.0" | eval st = strptime(START_TS, "%Y-%m-%d %H:%M:%S.%N") | eval et = strptime(END_TS, "%Y-%m-%d %H:%M:%S.%N") | eval diff = et - st | eval dur = tostring(diff, "duration")

More info on the date variables can be found here: https://docs.splunk.com/Documentation/Splunk/6.6.1/SearchReference/Commontimeformatvariables

[3no]

Ok,

let's give this a try, then :

my search query 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime("START_TS")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime("END_TS")
| eval duration = END_TS - START_TS

3no

[DalJeanis]

@3no - this should work, but you need to account for the ".%1Q" in the original field.

[DalJeanis]

@locose -

First, the difference between strftime and strptime is f for FORMAT, p for PULL.

strftime takes data that is in epoch form, and formats it forward to human-readable form.

strptime takes time data that is formatted for display, and strips ( strps) it back into epoch time, perfect for performing productive calculations. In this case, you want strptime, as @3no said.

Second, whichever direction you are going, each piece of the display format needs to be exactly right.

%y is 2-digit year, %Y is 4-digit year.

Also, both %N and %Q are for sub-second components, and one defaults to 3 digits, the other to 6 digits. Since you have exactly one digit, neither default will work and you must specify the 1 - %1Q or %1N are fine. Also, there is a period . between seconds and sub-seconds in your fields, not a colon :.

So, to properly extract your times...

| eval Starttime=strftime(START_TS,"%y-%m-%d %H:%M:%S:%N")

...should be...

| eval Starttime=strptime(START_TS,"%Y-%m-%d %H:%M:%S.%1N")

... and then when you subtract the two, your difference in epoch time will read out as the number of seconds between the two times.

[locose]

didnt work. Tried

| eval starthere_time=strptime(START_TS,"%y-%m-%d %H:%M:%S:%N")
| eval endhere_time=strptime(END_TS,"%y-%m-%d %H:%M:%S:%N")
|eval duration = endhere_time - starthere_time

[3no]

Hey locose,

Try with strptime instead of strftime.

If it doesn't work try to change the name of your variable because starttime and endtime are already used by splunk (and I'm not sure about how it react about that):

https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/SearchTimeModifiers

3no.

[DalJeanis]

@3no - splunk is case-sensitive in field names, so your comment about starttime and endtime is not part of the issue.