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

[auaave]

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!

[mayurr98 · Accepted solution]

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!

[auaave]

Hi @ mayur98,

The above query worked! Thanks a lot!

[gcusello]

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

[auaave]

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

[gcusello]

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

[ttovarzoll]

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