topic Re: How to convert seconds to [h]:mm:ss? in Splunk Search

How to convert seconds to [h]:mm:ss?

auaave — Tue, 09 Jan 2018 09:36:55 GMT

Hi Guys!

I have an error duration in seconds, how can I convert it to [h]:mm:ss?

I used the below query but the if the total hours is 25hrs, it is showing as 1d + 1h.

| stats sum(DURATION) AS "DURATION"
| eval HOURS=tostring(DURATION,"duration")

Thank you!

Re: How to convert seconds to [h]:mm:ss?

gcusello — Tue, 09 Jan 2018 11:41:15 GMT

Hi auaave,
did you tried with a simple division?

 | stats sum(DURATION) AS "DURATION"
 | eval HOURS=DURATION/3600

eventually you can round it:

 | stats sum(DURATION) AS "DURATION"
 | eval HOURS=round(DURATION/3600,0)

If you want also minutes and seconds, you can follow the same way:

 | stats sum(DURATION) AS "DURATION"
 | eval 
          HOURS=round(DURATION/3600,0), 
          MINUTES=round((DURATION-HOURS*3600)/60,0), 
          SECONDS=DURATION-HOURS*3600-MINUTES*60, 
          TIME=HOURS.":".MINUTES.":".SECONDS

Bye.
Giuseppe

Re: How to convert seconds to [h]:mm:ss?

mayurr98 — Tue, 09 Jan 2018 15:42:12 GMT

hey @auaave

try this run anywhere search

| makeresults 
| eval DURATION=90126 
| eval secs=DURATION%60,mins=floor((DURATION/60)%60),hrs=floor((DURATION/3600)%60) 
| eval HOURS=if(len(hrs)=1,"0".tostring(hrs), tostring(hrs)),MINUTES=if(len(mins)=1,"0".tostring(mins), tostring(mins)),SECONDS=if(len(secs)=1,"0".tostring(secs), tostring(secs)) 
| eval Time=HOURS.":".MINUTES.":".SECONDS | table DURATION HOURS MINUTES SECONDS Time

For your query

<your_base_Query> 
| stats sum(DURATION) AS "DURATION" 
| eval secs=DURATION%60,mins=floor((DURATION/60)%60),hrs=floor((DURATION/3600)%60) 
| eval HOURS=if(len(hrs)=1,"0".tostring(hrs), tostring(hrs)),MINUTES=if(len(mins)=1,"0".tostring(mins), tostring(mins)),SECONDS=if(len(secs)=1,"0".tostring(secs), tostring(secs)) 
| eval Time=HOURS.":".MINUTES.":".SECONDS

Let me know if this works for you!

Re: How to convert seconds to [h]:mm:ss?

auaave — Wed, 10 Jan 2018 02:12:42 GMT

Hi @cusello,

Thanks for your reply! I tried the below query but the result is still showing the duration in seconds.
Can you let me know what is wrong with the below?

| stats sum(DURATION) AS "DURATION" 
| eval HOURS=round(DURATION/3600,0) 
| eval MINUTES=round((DURATION-HOURS*3600)/60,0)
| eval SECONDS=DURATION-HOURS*3600-MINUTES*60
| eval TIME=HOURS.":".MINUTES.":".SECONDS

Re: How to convert seconds to [h]:mm:ss?

auaave — Wed, 10 Jan 2018 02:13:57 GMT

Hi @ mayur98,

The above query worked! Thanks a lot!

Re: How to convert seconds to [h]:mm:ss?

gcusello — Wed, 10 Jan 2018 07:37:00 GMT

try to use floor instead round function:

 | stats sum(DURATION) AS "DURATION" 
 | eval HOURS=floor(DURATION/3600,0) 
 | eval MINUTES=floor((DURATION-HOURS*3600)/60,0)
 | eval SECONDS=DURATION-HOURS*3600-MINUTES*60
 | eval TIME=HOURS.":".MINUTES.":".SECONDS

Bye.
Giuseppe

Re: How to convert seconds to [h]:mm:ss?

ttovarzoll — Thu, 18 Feb 2021 17:57:22 GMT

floor(X) is the correct command -- round() is wrong because it also rounds 'up'

However, there is no second value in floor(X), you don't need to specify the ",0"

 | stats sum(DURATION) AS "DURATION" 
 | eval HOURS=floor(DURATION/3600) 
 | eval MINUTES=floor((DURATION-HOURS*3600)/60)
 | eval SECONDS=DURATION-HOURS*3600-MINUTES*60
 | eval TIME=HOURS.":".MINUTES.":".SECONDS