topic How to format 86401 seconds as 24:00:01 in Splunk Search

How to format 86401 seconds as 24:00:01

SammyDavis — Fri, 22 Jan 2016 18:57:53 GMT

I am trying to display a duration result to a dashboard and when I try to use the function to convert seconds to HH:MM:SS format like this:
index="ourlogs" | eval OurNameDDHHMMSS=tostring(OurVariable, "duration")

Anything over 24 hours (86400 seconds) the result I see is 1+00:00:01. When I do a search and I ask for the top 2 (using head 2) I am getting the 23:00:00 numbers returned instead of anything with the 1+ format. I also think for the people who look at the dashboard it would be easier for them to see it as 24:00:01.

Any suggestions or links would be appreciated.

Thanks

Re: How to format 86401 seconds as 24:00:01

grijhwani — Sat, 23 Jan 2016 01:49:59 GMT

84600 seconds is actually 23:30. I think you mean 86400.

Re: How to format 86401 seconds as 24:00:01

somesoni2 — Sat, 23 Jan 2016 22:47:02 GMT

You can sort based on time in seconds and then convert it to duration to keep the sorting correct. If you still want to show time HH:MM:SS instead of D+HH:MM:SS, then you can calculate and concatenate each part separately.

Re: How to format 86401 seconds as 24:00:01

Richfez — Sun, 24 Jan 2016 01:44:11 GMT

I was honestly surprised at how hard this was. That usually means I'm doing it wrong, but I did find a way to do it. I too have noticed this behavior, but it affects our data very little so I haven't "fixed" it. That being said, you just gave me incentive.

Try this run-anywhere search. In it you should have all bits and pieces you need.

| gentimes start=10/1/15 end=10/5/15 
| eval test=86401 
| eval secs=test%60 | eval mins=floor((test/60)%60) | eval hrs=floor((test/3600)%60)
| eval f_hrs=if(len(hrs)=1,"0".tostring(hrs), tostring(hrs))
| eval f_mins=if(len(mins)=1,"0".tostring(mins), tostring(mins))
| eval f_secs=if(len(secs)=1,"0".tostring(secs), tostring(secs))
| eval result=f_hrs.":".f_mins.":".f_secs

My naming: test is the value I'm testing (use your own variable there). secs, mins and hrs are the converted hours, minutes and seconds from the test value. f_* are the "formatted" converted values (e.g. adding a leading zero when necessary) and the result is just concatenating those values together.

NOTES: This is nasty. I tried to tame it as best I could, but I'm sure this code would make fun of your grandmother and try to kick your puppy if it was given a chance. I tried convert(), but it seems that "Hours" in Splunk are less than 24, regardless of your desires. That makes total sense for times but not necessarily for durations.

I will be putting in an Enhancement Request for some option to convert durations without that implicit day-conversion. On a good day I can read numbers well into the upper two digits (like even all the way up to 73!), so changing "27" into "1 day plus 3 hours" isn't always helpful.

Re: How to format 86401 seconds as 24:00:01

SammyDavis — Tue, 26 Jan 2016 17:41:55 GMT

Correct thanks

Re: How to format 86401 seconds as 24:00:01

SammyDavis — Tue, 26 Jan 2016 18:18:26 GMT

Thanks very much for your help, this did work to display it in the format I wanted and give me the results I was looking for. As one of my teachers liked to tell me over and over again "working code is good code" so I am sure this one would avoid kicking any puppies, but not sure about not making fun of grandmothers.

Re: How to format 86401 seconds as 24:00:01

erritesh17 — Tue, 11 Jul 2017 13:13:48 GMT

Hey ,
I got same problem but instead of 86401 sec I have 580397 sec, as per rich7177 answered I am getting 41:13:17 as result , because when I convert 580397sec to HH:MM:SS I am getting 6 days 17:13:17 as answer. So tell me how to solve this problem.

Re: How to format 86401 seconds as 24:00:01

KailA — Tue, 11 Jul 2017 13:40:24 GMT

Hi,

This is another easiest way to do it :

| gentimes start=10/1/15 end=10/5/15 
| eval test=86401
| eval Hour = floor(test/3600)
| eval test = strftime(test,"%M:%S")
| eval result = Hour.":".test

Re: How to format 86401 seconds as 24:00:01

sbbadri — Tue, 11 Jul 2017 14:01:13 GMT

| eval ts="580397" | convert rmunit(ts) as numSecs | eval stringSecs=tostring(numSecs,"duration") | eval stringSecs=case(stringSecs="00:00:00", "0+0:0:0", 0=0, stringSecs) | eval stringSecs = replace(stringSecs,"(\d+):(\d+):(\d+)","\1h \2min \3s") | table ts stringSecs

Re: How to format 86401 seconds as 24:00:01

DalJeanis — Tue, 11 Jul 2017 14:23:39 GMT

Really no need to do all those format conversions. strftime will do most of the work, even if the duration in seconds has not been labeled as such.

| makeresults
| eval ts=580397
| eval hours = floor(ts/3600)
| eval tshoursformatted=hours.":".strftime(ts-3600*hours,"%M:%S")

...or, if you prefer the result in days...

| makeresults
| eval ts=580397
| eval days = floor(ts/86400)
| eval tsdaysformatted=days." days + ".strftime(ts-86400*days,"%H:%M:%S")

Re: How to format 86401 seconds as 24:00:01

DalJeanis — Tue, 11 Jul 2017 14:25:41 GMT

@KailA - Really close, but you need to use floor instead of round. Otherwise, half the time you will get the wrong number of hours.

Re: How to format 86401 seconds as 24:00:01

KailA — Tue, 11 Jul 2017 15:00:14 GMT

You're right ! My bad 🙂

Re: How to format 86401 seconds as 24:00:01

gabriel_vasseur — Tue, 19 Apr 2022 15:31:36 GMT

You do not want the %60 in:

| eval hrs=floor((test/3600)%60)

Otherwise a duration of 66 hours will become 06 hours...

Re: How to format 86401 seconds as 24:00:01

bowesmana — Tue, 19 Apr 2022 23:29:40 GMT

@Richfez

printf is a useful eval func here to do the formatting

| eval result=printf("%02d:%02d:%02d", floor(test/3600), floor((test/60)%60), test%60)