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!
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!
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!
Hi @ mayur98,
The above query worked! Thanks a lot!
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
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
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
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