mySQL ROUND()?

Moving on from the great answer posed by r937 yesterday, I have Perplexing issue with ROUND()… This has to be a bug…Right!?
I have a number, a result of the query below which I want to round to 2 decimal places and show as a percentage.

When I round to 1 decimal place I get 2.4 - all very nice and what I expected.
Also, when I round to 3 I get 2.463 - again all is still good in my rather dim little world
However, when I go for 2 decimal places I get 2.4699999999999998
WTF…!? Is it me… or does MySQL have a decimal allergy?

CONCAT(
	ROUND(
		(TIMESTAMPDIFF(second,h2.updated,h1.updated)/(DAY(LAST_DAY(h2.updated))*86400)*100),2),'%')

mysql bugs are exceedingly rare – more often it’s PEBKAC

please show the values for h1.updated and h2.updated that produced your error

@r937 Thanks for PEBKAC, I was guessing that might be the case but I much prefer FTTKNWTD™ . If it is PEBKAC then why is it OK in certain scenarios and not other - or is that me just being lucky?
h1.updated: 2016-12-06 21:05:39
h2.updated: 2016-12-06 21:05:20
But the above h1 and h2 are taken from when I ‘GROUP BY’ month
After further investigation it appears that things work OK until I group by… confused

FTTKNWTD - Forgive Them, They Know Not What They Do!

here you go… this is how to debug a formula

~you~ figure out what’s wrong, because i don’t see how the two datetimes that you gave me could possibly produce the results 2.463 or 2.4699999999999998

[code]SET @h1_updated = ‘2016-12-06 21:05:39’ ;
SET @h2_updated = ‘2016-12-06 21:05:20’ ;

SELECT diff
, last
, d
, calc
, CONCAT(ROUND(calc,2),‘%’) AS result
FROM ( SELECT diff
, last
, d
, diff / ( d * 86400 ) * 100 AS calc
FROM ( SELECT TIMESTAMPDIFF(second,@h2_updated,@h1_updated) AS diff
, LAST_DAY(@h2_updated) AS last
, DAY(LAST_DAY(@h2_updated)) AS d
) AS x
) AS y

diff last d calc result


19 2016-12-31 31 0.0007 0.00%
[/code]

aw, shit… don’t tell me you forgot how to GROUP BY

@r937 Really appreciate you sharing that debugging a formula… It helps.
In response to your second reply… I’m so sorry if I forgot something, truly I am, but there are days Rudy when things are so clear and then there are others, when its not… when I look at some of the code I’ve written and I cannot grasp it. I think, who the hell wrote that… Weird, isn’t it…SQL is somewhat like that for me. Some days its as clear as a bell and some days it makes no sense whatsoever. Perhaps today is one of those days… In any case, I appreciate your continued yet frustrated support.

@r937 I finally figured out it wasn’t GROUP BY or ROUND() that was the issue. It was the CONCAT(…,‘%’) statement, removing that and everything fell into place… But your debug query was still very helpful… Thanks again

i’m not buying that, sorry

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.