Something weird with trailing zeros using TOSTRING

So, I'm trying to display downtime in my modern dashboard using the following code:

--------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------

which basically says if the down time is longer than 1-day (1440) mins divide it by 1440 and tack on 'days' however for some reason the TOSTRING function is adding lots of trailing zeros:

Using only ROUND() produces a result with no trailing zeros but then I am unable to add 'days'. This method however does not include the trailing zeros when performed on alerts for some reason. I've also tested using the MINUTEDIFF function which seems to have no effect.

-----------------------------------------------------------------------------------------------------------

If anyone has any advice, please let me know

Find more posts tagged with

Functions

tostring

SWQL

Modern Dashboards

SWQL Studio

Accepted answers

HelpMeTHWACK

@wluther, @Seashore I combined both your ideas into this. Since using 1440.0 returned a long decimal number which caused trailing zeros but using 1440 returned an integer there wasnt a simple way to achieve "378.9 days", therefore by using the method below I managed something close, I could have used this to obtain 378.9 but I decided to do days and hours since I think it looks better.

---------------------------------------------------------------------------------------------------------------

,CASE 
    WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin > 1440 AND [Nodes].Status != 1) -- >1day
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 1440, 1)) + ' Days ' + TOSTRING( ([Nodes].NodeDowntimeHistory.TotalDurationMin - (ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 1440, 1) * 1440))/60 ) + ' Hours')
	WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin > 60 AND [Nodes].Status != 1) -- >1hr
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 60, 1)) + ' Hours ' + TOSTRING( ([Nodes].NodeDowntimeHistory.TotalDurationMin - (ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 60, 1) * 60)) ) + ' Mins') 
	WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin < 60 AND [Nodes].Status != 1) -- <1hr
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin, 1)) + ' Minutes')
    ELSE ('ONLINE')
END AS [Active]</pre>-----------------------------------------------------------------------------------------------------------Thank you so much for your help

All comments

wluther

@HelpMeTHWACK I use an old case statement I swiped from someone else on THWACK, probably 100 years ago, but it still seems to work well so far.

,Case
	When	Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400)>0
		Then	ToString(ToString(Floor((SecondDiff(e.EventTime,GetDate()) +0.0)/86400))+'d '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	When	Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600)>0
		Then	ToString(ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	When	Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60)>0
		Then	ToString(ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	Else ''
End AS [Duration]

Does this one do what you are asking?

Thank you,

-Will

wluther

My initial reply was blocked, for whatever reason(s), so let's see if this reply makes it through or not...

This is what I have used for a while now, which I originally found floating around on THWACK.

Case
	When	Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400)>0
		Then	ToString(ToString(Floor((SecondDiff(e.EventTime,GetDate()) +0.0)/86400))+'d '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	When	Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600)>0
		Then	ToString(ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 86400*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/86400))) + 0.0)/3600))+'h '+ ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	When	Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60)>0
		Then	ToString(ToString(Floor(((SecondDiff(e.EventTime,GetDate()) - 3600*(Floor((SecondDiff(e.EventTime,GetDate()) + 0.0)/3600))) + 0.0)/60))+'m ')
	Else ''
End AS [Duration]

I hope this gets you what you need, and hopefully this reply doesn't flagged and blocked too. If both of my replies eventually make it through, please note they should be showing the same SWQL query snippet.

Thank you,

-Will

Seashore

Did some testing here:

So you see part of your code here. [Text4] gives the same result as you have. either you do as in [Text2] or [Text5]. The main thing is to remove the ".0" you have added.

Hope that works for you.

(Next time, please add the code not only as picture, easier to test then. You can "insert"/"code" and choose SQL as language. )

HelpMeTHWACK

This does work well, although the only issue here is this method doesn't allow for any decimal places in the result, you're stuck with an integer. I'll try adding the decimal on and see if that works

HelpMeTHWACK

---------------------------------------------------------------------------------------------------------------

,CASE 
    WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin > 1440 AND [Nodes].Status != 1) -- >1day
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 1440, 1)) + ' Days ' + TOSTRING( ([Nodes].NodeDowntimeHistory.TotalDurationMin - (ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 1440, 1) * 1440))/60 ) + ' Hours')
	WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin > 60 AND [Nodes].Status != 1) -- >1hr
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 60, 1)) + ' Hours ' + TOSTRING( ([Nodes].NodeDowntimeHistory.TotalDurationMin - (ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin / 60, 1) * 60)) ) + ' Mins') 
	WHEN ([Nodes].NodeDowntimeHistory.TotalDurationMin < 60 AND [Nodes].Status != 1) -- <1hr
		THEN (TOSTRING(ROUND([Nodes].NodeDowntimeHistory.TotalDurationMin, 1)) + ' Minutes')
    ELSE ('ONLINE')
END AS [Active]</pre>-----------------------------------------------------------------------------------------------------------Thank you so much for your help