topic Re: How to convert date differences to seconds in Splunk Search

How to convert date differences to seconds

pipipipi — Thu, 12 Mar 2020 04:31:45 GMT

Hi all,

I have a lookup like this.

    caseid date
    a          19-01-01 15:54:43.934000000
    b          19-01-01 16:54:43.934000000
    c          19-01-01 17:54:43.934000000
    d          19-01-01 18:54:43.934000000
    e
    f
    g
    .
    .
    .
    .
    .

I did this command

| inputlookup test1
    | eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
    | stats min(date) as starttime max(date) as endtime by caseid
    | eval diff =endtime-starttime 
    | stats avg(diff) as average

my result is like this.(this is dummy)

average
999999.9999999

I want to get results like this.

average
3600

(this is 1 hour)
I understand I have to convert, but I don't know how to convert date differences to seconds.
Could you help me?

Re: How to convert date differences to seconds

anmolpatel — Thu, 12 Mar 2020 04:52:02 GMT

As such:

| makeresults 
| eval _raw = "caseid,date
 a, 19-01-01 15:54:43.934000000
 b, 19-01-01 16:54:43.934000000
 c, 19-01-01 17:54:43.934000000
 d, 19-01-01 18:54:43.934000000" 
| multikv forceheader=1 
| eval date_new=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| eventstats min(date_new) as starttime max(date_new) as endtime count as totalevents 
| eval avg = round((endtime-starttime) / totalevents, 1)

Re: How to convert date differences to seconds

kamlesh_vaghela — Thu, 12 Mar 2020 04:56:38 GMT

@pipipipi

Do you want to round?
Like by adding this in below search?

| eval average=round(average,0)

I tried your search with different data it is working.

| makeresults 
| eval _raw=" caseid date
 a          19-01-01 15:54:43.934000000
 b          19-01-01 16:54:43.934000000
 c          19-01-01 17:54:43.934000000
 d          19-01-01 18:54:43.934000000
 a          19-01-01 18:54:43.934000000
 b          19-01-01 19:54:43.934000000
 c          19-01-01 20:54:43.934000000
 d          19-01-01 21:54:43.934000000" 
| multikv forceheader=1 
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| stats min(date) as starttime max(date) as endtime by caseid 
| eval diff =endtime-starttime 
| stats avg(diff) as average | eval average=round(average,0)

Please let us know if you have different expectations.

Thanks

Re: How to convert date differences to seconds

pipipipi — Thu, 12 Mar 2020 05:04:07 GMT

Thank you for helping me.
Sorry, my English is bad.

I want to convert 99999.9999(this is example value) to 3600.

| eval diff =endtime-starttime 
| stats avg(diff) as average

is date difference, I want to express the date difference in seconds

Thank you for helping

Re: How to convert date differences to seconds

pipipipi — Thu, 12 Mar 2020 05:08:49 GMT

Thank you for helping me.
I have about 15000 column, so,
|eval _raw........ is difficult for me.

Thank you so much.

Re: How to convert date differences to seconds

anmolpatel — Wed, 30 Sep 2020 04:33:19 GMT

@pipipipi the _raw line is to demonstrate the example.

In your case, you will have the following:
| inputlookup test1
| eval date_new=strptime(date,"%y-%m-%d %H:%M:%S.%9N")
| eventstats min(date_new) as starttime max(date_new) as endtime count as totalevents
| eval avg = round((endtime-starttime) / totalevents,1)

Re: How to convert date differences to seconds

woodcock — Thu, 12 Mar 2020 06:23:14 GMT

Like this:

| inputlookup test1
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N") 
| stats range(date) AS diff BY caseid
| stats avg(diff) AS average
| fieldformat average = tostring(average, "duration")

Re: How to convert date differences to seconds

to4kawa — Thu, 12 Mar 2020 09:48:11 GMT

999999.9999999 *+-/ ? = 3600

what's your formula?

Re: How to convert date differences to seconds

pipipipi — Fri, 13 Mar 2020 00:38:51 GMT

Thank you for helping me.
When I using tostring, What is the unit of time? I think seconds, is it right?

Re: How to convert date differences to seconds

jinseong — Wed, 30 Sep 2020 04:33:42 GMT

hello.

|eval temp=strptime(start_time, "%Y-%m-%d %H:%M:%S.%Q")
|eval temp1=temp+duration
|eval end_time=strftime(temp1,"%Y-%m-%d %H:%M:%S.%Q")

|table start_time end_time duration
|stats avg(duration) as qwe

|eval HH=floor(qwe/3600)
|eval MM=floor((qwe-(HH*3600))/60)
|eval SS=round(qwe-(HH*3600)-(MM*60),0)

|eval avg_duration=(HH + ":" + MM + ":" +SS)
|fields avg_duration

I expressed the hour and minute by the following command. It is possible by subtracting the minute from the average time and subtracting the minute from this part.

Re: How to convert date differences to seconds

woodcock — Mon, 16 Mar 2020 11:02:26 GMT

It auto-ranges the output but keeps the actual value as seconds stored as a number (unitless).