Menu
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.
805
Integers and floats are two different kinds of numerical data. An integer (more commonly called an int) is a number without a decimal point. A float is a floating-point number, which means it is a number that has a decimal place. Floats are used when more precision is needed.
If doing math with floats, you need to add a decimal point, otherwise it will be treated as an int. See the Floating point constants page for details. The float data type has only 6-7 decimal digits of precision. That means the total number of digits, not the number to the right of the decimal point.
1.2 is a double (8 bytes). 1.2f is a float (4 bytes). Save this answer.
For most use cases, I recommend using decimals. If you initialize them with strings, you prevent subtle bugs and get the increased precision benefits. If you need to eliminate all subtle rounding issues, use the fractions module. Even though floats perform better than decimals, I recommend avoiding floats.
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.
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
