Splunk Search

## How do I convert epoch time to HH:MM:SS?

Path Finder

Background

So I have two date fields - Date_Created & Acknowledge_Date both in the format `YYYY-MM-DD HH:MM:SS`. I wish to work out the difference of these two times and then create an average of all the results - essentially this -> `Average(Acknowledge_Date-Date_Created)`

Search
I have created the following search. This has converted the times to epoch, subtracted them, and provided an average of the epoch time.

`````` index="Cyber" sourcetype=Response queue = "Incident" status ="resolved"  | dedup ticket

| table Date_Created, Acknowledge_Date
| eval epoch1=strptime(Date_Created,"%Y-%m-%d %H:%M:%S")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Date_Created) as epochDateCreated
| eval epoch2=strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Acknowledge_Date) as epochAck
| eval Diff=(epochAck-epochDateCreated)

|stats avg(Diff)
``````

Problem
I now have an average number in epoch format - 5848.333333 - is there a way to convert this into HH:MM:SS

Any help will be much appreciated

Tags (4)
Explorer

If you wanted to look at the average time in hours that an incident ticket was open you could use this search

``````index="Cyber" sourcetype=Response queue="Incident" status="resolved"
| dedup ticket
| eval Diff=floor(((strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S"))-(strptime(Date_Created,"%Y-%m-%d %H:%M:%S")))/(3600))
| stats avg(Diff)
``````

Let me know if this works for you.

Path Finder

Hi tmccamant,

Afraid this hasn't worked as it has left me with the result of -15.208333

Any other ideas will be greatly appreciated

Motivator

`````` | eval DiffTime=strftime(Diff, "%H:%M:%S")
``````
Path Finder

Hi DMohn. | eval DiffTime=strftime(Diff, "%H:%M:%S") helps me convert the epoch time to the format I want. However I cannot appear to get | stats avg(DiffTime) when it is in this format.

I also cannot use |stats avg(Diff) before making the conversion.

Is there a solution to this?

Motivator

hi mjd555,

and look the function `strftime(X,Y)`

Path Finder

Hi Gyslainlatsa, thanks for your help. However once I have added strftime to the code, I cannot average my results.

I also cannot use | stats avg(Diff) before making my conversion.

Is there a solution to this?

Path Finder

Hi gyslainlatsa, so I strftime has helped me see the time difference in HH:MM:SS - however I cannot avg these results. Is there a way to do this? Thanks again!

`````` index="Cyber" sourcetype=Response queue = "Incident" status ="resolved"  | dedup ticket

| table Date_Created, Acknowledge_Date
| eval epoch1=strptime(Date_Created,"%Y-%m-%d %H:%M:%S")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Date_Created) as epochDateCreated
| eval epoch2=strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Acknowledge_Date) as epochAck
| eval epochDiff=(epochAck-epochDateCreated)
| eval normalDiff=strftime(epochDiff, "%H:%M:%S")
``````