Archived Forum

Some progress (sorry for spamming)

The value seems to be well stored in mysql but MySQL round it when retrieving the value ...

If I post http://xxx/input/post.json?node=1&csv=123456499

SELECT value FROM `input` WHERE id=4220  returns  123456000
SELECT ROUND(value,0) FROM `input` WHERE id=4220  returns  123456496 but not 123456499

Weird ?

Hi,

Could someone tell me whether the same problem occurs (or not) on its own emoncms server ?

Thank's.

Eric

EDIT : This problem occurs on my VPS (Ubuntu & EmonCMS 8.3.6) and also on my linux shared host (EmonCMS 8.5.1).

Hi Eric, sorry nobody has confirmed this for you, I don't have a local emoncms right now so cannot do any tests. But I do recall doing many tests when I was developing the datacodes stuff for emonhub and I always got 7 digits of precision not 6 and that was definitely since v8.3.6.

Working purely on "what could be different" since you are using multiple platforms and versions as was I to get 7 not 6 digits and since emoncms.org is hosted on a uk server I wonder if localization settings are effecting anything? I briefly searched on this and it appears it can be an issue,

In the last example you gave, I think the 123456496 vs 123456499 may just be a float precision issue as 8 digits are correct.

Paul

Hi,

Thank's Paul for replying.

As explained here, "floating-point values are approximate and not stored as exact values.  [...]  MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point."

M and D were not mentioned in my databases. As explained here "If M and D are omitted, values are stored to the limits permitted by the hardware."

So I tried many different values for M and D but I still got wrong values.

Endly, as suggested on many forums, I modified the type of the column "value" from FLOAT to DECIMAL and now it works fine. (By default M=10 and D=0 for DECIMAL)

Eric

Hi, first of all why are you saving such a large number?

EmonCMS is using 32bits float on all its engines. Float data type is 4bytes = 32 bits, packed as in IEEE 754.

Floats are approximate and not stored as exact values.

On 32 bits IEEE 754 the closest value to 123456499 is 123456496.

Test here: http://www.h-schmidt.net/FloatConverter/IEEE754.html

I'm not sure why the select returns 123456000, but if you do :

SELECT ROUND(value,2) FROM `input`

You will get 123456496.00 that is correct as the closest number, and the value is indeed correctly saved as float on the db.

So the options are to change on all engines Float to :

• Double and use 8bytes
• Decimal and still only use 4bytes
• Decimal with more bytes

On mysql every 9 leftover digits requires 4 bytes see https://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-character...

To keep the same 4 bytes we can only use:

• Decimal(6,2) max value 9999.99, 0.001 would be rounded to 0.
• Decimal(9,0) max value 999999999 losing decimal precision.

We have much more options with float for 4bytes even if losing precision on very large or very small numbers.

Passing from 4 to 8 bytes will praticaly double the required space for php engines, not good

So how much decimal precision in required and how large (or small) the value can be that fits everyone?

Hi Chaveiro,

Thank you for great reply !

Hi, first of all why are you saving such a large number?

The EmonTx default sketch sends the accumulated Wh. With 'large' power, large number are quickly reached.  15 kW for  one month corresponds to 15000*24*30=  10800000 Wh.

I'm also using another sketch on EmonTH that is reading my electric meter. It also reports the total elapsed Whs (between 0 and 999999999). I have to store the exact Wh number because I'm calculating the power from the  accumulated Wh.

For my needs, I don't send/store any decimal number, so, I will change the data type in MySQL to Decimal(9,0).

Anyway, I think it's a big limitation.

Eric