Splunk Search

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

auaave
Communicator

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!

1 Solution

mayurr98
Super Champion

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!

View solution in original post

mayurr98
Super Champion

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
Communicator

Hi @ mayur98,

The above query worked! Thanks a lot!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

auaave
Communicator

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
0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...