Splunk Search
Highlighted

## How to convert date differences to seconds

Path Finder

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?

Tags (3)
1 Solution
Highlighted

## Re: How to convert date differences to seconds

Builder

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"
| 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)
``````
Highlighted

## Re: How to convert date differences to seconds

Path Finder

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

Thank you so much.

Highlighted

## Re: How to convert date differences to seconds

Builder

@pipipipi the _raw line is to demonstrate the example.

In your case, you will have the following:
| inputlookup test1
| eval datenew=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)

Highlighted

## Re: How to convert date differences to seconds

SplunkTrust

@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"
| 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

Highlighted

## Re: How to convert date differences to seconds

Path Finder

Thank you for helping me.

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

Highlighted

## Re: How to convert date differences to seconds

Esteemed Legend

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")
``````
Highlighted

## Re: How to convert date differences to seconds

Path Finder

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

Highlighted

## Re: How to convert date differences to seconds

Esteemed Legend

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

Highlighted

## Re: How to convert date differences to seconds

Ultra Champion
``````999999.9999999 *+-/ ? = 3600
``````