Coverting EPOCH time to Hours, Minutes, Seconds format

[jackreeves]

I have an issue where I have a number of fields in my data in following format of "%H:%M:%S" and they are stored as text. Therefore I cannot do any stats calculations such as avg on these fields.

I am trying to convert these fields in EPOCH time using the strptime, this then allows me to calculate an average & convert back to "%H:%M:%S" format.

However, some of the calculations aren't converting back correctly.

Full search query:
sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG
| eval actual_stack_time1=strptime(actual_stack_time,"%H:%M:%S")
| eval handling_time1=strptime(handling_time,"%H:%M:%S")
| stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day DOW
| eval "Avg. Time on Stack"=strftime(actual_stack_time1, "%H:%M:%S")
| eval "Avg. Handling Time"=strftime(handling_time1,"%H:%M:%S")
| fields Day DOW "Call Volume" "Avg. Handling Time" "Avg. Time on Stack"

EXAMPLE before adding the strftime syntax:
Day DOW Call Volume actual_stack_time1 handling_time1
06/03 Tue 24 1521072478.75 1521072078.1666667

This should then convert to 01:01:18 and 01:07:58 but currently is returning as 00:01:18 and 00:07:58.

Can anyone help?

Thanks,
Jack

[p_gurav]

Hi,

Can you try:

     sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG Day=06/03/2018 
     | convert num(actual_stack_time) as "actual_stack_time1" 
     | convert num(handling_time) as "handling_time1" 
     | stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day
     | eval handling_time1=round(handling_time1,0) 
     | convert timeformat="%H:%M:%S" ctime(actual_stack_time1)
      | convert timeformat="%H:%M:%S" ctime(handling_time1)

[jackreeves]

Still returning same result 😞

[jackreeves]

It appears to be adding an extra hour to every value. If I amend the Time Zone in administrator settings this also impacts it?

Any ideas?

[tiagofbmm]

Hey

Sorry but I checked the values and it should convert to 00:01:18 and 00:07:58, check here https://www.epochconverter.com/

[jackreeves]

Unsure what is going wrong because when I calculate the avg in Excel it is 01:01:18 and 01:07:58.

I've amended the search to the following:

sourcetype=diags_yesterday Priority=A "Next Ind"!=C Team=DIAG Day=06/03/2018 
| convert num(actual_stack_time) as "actual_stack_time1" 
| convert num(handling_time) as "handling_time1" 
| stats count as "Call Volume" avg(actual_stack_time1) as "actual_stack_time1" avg(handling_time1) as "handling_time1" by Day
| eval handling_time1=round(handling_time1,0) 
| eval "Avg. Time on Stack"=strftime(actual_stack_time1,"%H:%M:%S") 
| eval "Avg. Handling Time"=strftime(handling_time1,"%H:%M:%S")

Now returning with the values of 02:01:18 and 02:07:58. The actual_stack_time1=4078.75secs and handling_time1=3678secs. This converted should equal 01:01:18 and 01:07:58