I'm building this API, and the database will store values that represent one of the following:
I honestly have no idea how to represent something that the range is between 0 and 100% in numbers. Should it be
Is there a clear choice for that? A global way of representing on databases something that goes from 0 to 100% percent? Going further, what's the correct that type for it, float or decimal?
Thank you.
I'll take the opposite stance.
FLOAT is for approximate numbers, such as percentages, averages, etc. You should do formatting as you display the values, either in app code or using the FORMAT() function of MySQL.
Don't ever test float_value = 1.3; there are many reasons why that will fail.
DECIMAL should be used for monetary values. DECIMAL avoids a second rounding when a value needs to be rounded to dollars/cents/euros/etc. Accountants don't like fractions of cents.
MySQL's implementation of DECIMAL allows 65 significant digits; FLOAT gives about 7 and DOUBLE about 16. 7 is usually more than enough for sensors and scientific computations.
As for "percentage" -- Sometimes I have used TINYINT UNSIGNED when I want to consume only 1 byte of storage and don't need much precision; sometimes I have used FLOAT (4 bytes). There is no datatype tuned specifically for percentage. (Note also, that DECIMAL(2,0) cannot hold the value 100, so technically you would need DECIMAL(3,0).)
Or sometimes I have used a FLOAT that held a value between 0 and 1. But then I would need to make sure to multiply by 100 before displaying the "percentage".
More
All three of "percentage, average, rate" smell like floats, so that would be my first choice.
One criterion for deciding on datatype... How many copies of the value will exist?
If you have a billion-row table with a column for a percentage, consider that TINYINT would take 1 byte (1GB total), but FLOAT would take 4 bytes (4GB total). OTOH, most applications do not have that many rows, so this may not be relevant.
As a 'general' rule, "exact" values should use some form of INT or DECIMAL. Inexact things (scientific calculations, square roots, division, etc) should use FLOAT (or DOUBLE).
Furthermore, the formatting of the output should usually be left to the application front end. That is, even though an "average" may compute to "14.6666666...", the display should show something like "14.7"; this is friendlier to humans. Meanwhile, you have the underlying value to later decide that "15" or "14.667" is preferable output formatting.
The range "0.00 - 100.00" could be done either with FLOAT and use output formatting or with DECIMAL(5,2) (3 bytes) with the pre-determination that you will always want the indicated precision.
I would generally recommend against using float. Floating point numbers do represent numbers in base-2, which causes some (exact) numbers to be round-up in operations or comparisons, because they just cannot be accurately stored in base-2. This may lead to suprising behaviors.
Consider the following example:
create table t (num float);
insert into t values(1.3);
select * from t;
| num |
| --: |
| 1.3 |
select * from t where num = 1.3;
| num |
| --: |
Base-2 comparison of number 1.3 fails. This is tricky.
In comparison, decimal provide an accurate representation of finite numbers within their range. If you change float to decimal(2, 1) in the above example, you do get the expected results.
I recommend using decimal(5,2) if you're going to store it in the same way you'll display it since decimal is for preserving the exact precision. (See https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html)
Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.
(https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html)
A floating-point value as written in an SQL statement may not be the same as the value represented internally.
For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.
https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html
Decimal : In case of financial applications it is better to use Decimal types because it gives you a high level of accuracy and easy to avoid rounding errors
Double : Double Types are probably the most normally used data type for real values, except handling money.
Float : It is used mostly in graphic libraries because very high demands for processing powers, also used situations that can endure rounding errors.
Reference: http://net-informations.com/q/faq/float.html
Difference between float and decimal are the precision. Decimal can 100% accurately represent any number within the precision of the decimal format, whereas Float, cannot accurately represent all numbers.
Use Decimal for e.g. financial related value and use float for e.g. graphical related value
mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*********************************************************************
@a := (a/3): 33.333333333
@b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100
The decimal did exactly what's supposed to do on this cases, it truncated the rest, thus losing the 1/3 part.
So for sums, the decimal is better, but for divisions, the float is better, up to some point, of course. I mean, using DECIMAL will not give you "fail-proof arithmetic" in any means.
I hope this will help.
In tsql:
Float, 0.0 store as 0 and it dont require to define after decimal point digit, e.g. you dont need to write Float(4,2).
Decimal, 0.0 store as 0.0 and it has option to define like decimal(4,2), I would suggest 0.00-1.00, by doing this you can calculate value of that percent without multiply by 100, and if you report then set data type of that column as percent as MS Excel and other platform view like 0.5 -> 50%.
