This is what I found when I had this doubt.
Use with AImysql> 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
*************************** 1. row ***************************
@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 a "fail proof arithmetic" in any means.
Answer from kanap008 on Stack OverflowI'm confused in differentiating between these two types of numbers. I'm a newbie.
Can anyone here please explain this?
mysql - Difference between float and decimal data type - Stack Overflow
floating point - Difference between decimal, float and double in .NET? - Stack Overflow
math - What is the difference between a floating decimal number and fixed decimal number? - Software Engineering Stack Exchange
field type: decimal vs. float
This is what I found when I had this doubt.
Use with AImysql> 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
*************************** 1. row ***************************
@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 a "fail proof arithmetic" in any means.
A "float" in most environments is a binary floating-point type. It can accurately store base-2 values (to a certain point), but cannot accurately store many base-10 (decimal) values. Floats are most appropriate for scientific calculations. They're not appropriate for most business-oriented math, and inappropriate use of floats will bite you. Many decimal values can't be exactly represented in base-2. 0.1 can't, for instance, and so you see strange results like 1.0 - 0.1 = 0.8999999.
Decimals store base-10 numbers. Decimal is an good type for most business math (but any built-in "money" type is more appropriate for financial calculations), where the range of values exceeds that provided by integer types, and fractional values are needed. Decimals, as the name implies, are designed for base-10 numbers - they can accurately store decimal values (again, to a certain point).
float (the C# alias for System.Single) and double (the C# alias for System.Double) are floating binary point types. float is 32-bit; double is 64-bit. In other words, they represent a number like this:
10001.10010110011
The binary number and the location of the binary point are both encoded within the value.
decimal (the C# alias for System.Decimal) is a floating decimal point type. In other words, they represent a number like this:
12345.65789
Again, the number and the location of the decimal point are both encoded within the value – that's what makes decimal still a floating point type instead of a fixed point type.
The important thing to note is that humans are used to representing non-integers in a decimal form, and expect exact results in decimal representations; not all decimal numbers are exactly representable in binary floating point – 0.1, for example – so if you use a binary floating point value you'll actually get an approximation to 0.1. You'll still get approximations when using a floating decimal point as well – the result of dividing 1 by 3 can't be exactly represented, for example.
As for what to use when:
For values which are "naturally exact decimals" it's good to use
decimal. This is usually suitable for any concepts invented by humans: financial values are the most obvious example, but there are others too. Consider the score given to divers or ice skaters, for example.For values which are more artefacts of nature which can't really be measured exactly anyway,
float/doubleare more appropriate. For example, scientific data would usually be represented in this form. Here, the original values won't be "decimally accurate" to start with, so it's not important for the expected results to maintain the "decimal accuracy". Floating binary point types are much faster to work with than decimals.
Precision is the main difference.
Float - 7 digits (32 bit)
Double-15-16 digits (64 bit)
Decimal -28-29 significant digits (128 bit)
Decimals have much higher precision and are usually used within financial applications that require a high degree of accuracy. Decimals are much slower (up to 20X times in some tests) than a double/float.
Decimals and Floats/Doubles cannot be compared without a cast whereas Floats and Doubles can. Decimals also allow the encoding or trailing zeros.
float flt = 1F/3;
double dbl = 1D/3;
decimal dcm = 1M/3;
Console.WriteLine("float: {0} double: {1} decimal: {2}", flt, dbl, dcm);
Result :
float: 0.3333333
double: 0.333333333333333
decimal: 0.3333333333333333333333333333
There are two major differences between the two:
- Binary format
- Intended use
For details of the binary format, and the math behind the binary format, see the Wikipedia articles for fixed point arithmetic and Floating point numbers.
Floating Point Numbers: Within the limits of the binary representation, floating point numbers represent variable precision. In short, you can represent really tiny numbers or really big numbers. The number of decimal places you can represent are only limited by the number of bits dedicated to the number. These are commonly used in physics and other more precise math problems.
Fixed Decimal Numbers: Have a constant number of digits after the decimal place. These are typically used to represent money, percentages, or a certain precision of the number of seconds (i.e. limiting to milliseconds). They are mostly used in databases as a simple and efficient storage format. The math involved with these types of numbers have no practical significance for any precision lower than the fixed number of decimal places. What practical use is 1/1000th of a penny?
How Floating Point Numbers Work:
Imagine that you have a fictional 'computer system' that can only store base-10 numbers in spaces that are 8 characters wide, AND it can only hold the numeric characters 0 through 9 but not the decimal character '.' . How should your system handle the variety of numbers that programmers need to use?
In a world that only has positive discrete values (0, 1, 2, ...) that are relatively small (less than 100 million) you won't have any problems fitting your numbers in the 8-character spaces: 0000 0000 is 0 and 9999 9999 is 1 less than 100 million.
However, we live in a world with numbers that are negative also. In real-world computer systems, most machines use the most significant bit to track if a value is positive or negative. In our fictional computer we would waste a lot of space using one whole character to track if a value is positive or negative. Instead of holding 100 million different values we'd be limited to only 20 million values (-10 million to +10 million)--we just lost 80 million values. One way to fix this is to use an offset--we shift all the values so that 5000 0000 is equal to 0, 9999 9999 is just less than 50 million, and 0000 0000 is -50 million. We've regained the 80 million values (or precision) that were lost before, but now we can only get to a magnitude of +/- 50 million instead of the 100 million we started with.
We can increase the range of the magnitude if we're willing to sacrifice precision. If we reserve the right-most 2 characters to store an exponential, then we can use the left-most 6 character for the precision. We still use an offset to handle negative numbers, which becomes 500 000, so our precision is limited to +/-500 thousand, but our 2 magnitude characters allows us to span a range from -5e104 to just under +5e104. With numbers that big, who cares if you're off by 1 or 2, or 300? If you had $5e104 (500000 99) in your pocket and a $300 (000300 00)bill fell out, you'd never know it.
So that's how we handle really big numbers in a space of only 8 character, but we don't have a way of handling fractional numbers. How do we represent PI, or e, or the Avagadro constant? One way is to pretend that there's a decimal point just to the left of each of our storage spaces--that means that the number 500000 00 represents the value 1/2 (.5). To represent the number 5 we use 500000 01 and to represent 500 trillion we use 500000 15. To represent PI, e, and the Avagadro constant we would use 314159 01, 271828 01 and 602214 24.
One last hurdle... ...How do we represent values that are much smaller than 1, i.e. how many meters is 1 Bohr? For this we have to have a way of representing negative numbers in our 2-character exponential space. We do this the same way we handle negative numbers in the precision space--we use an offset. With a 2-character space we choose an offset where 50 represents 0, 99 represents +49, and 00 represents -50. Now we can say that 1 Bohr is equal to 529177 38, or 5.29177e-11 meters.
This is the gist of how floating point numbers work. They always store as many precision characters as possible and use the exponential number to adjust for where the decimal point is in relation to the precision characters. As you can imagine, doing math on these numbers involves a lot of steps in order to account for differences in the exponential values. What number should we expect when we subtract 300000 03 from 500000 99 or divide 543201 76 by 123977 06?
How Fixed Point Numbers Work:
Because math with floating point numbers is so much slower than dealing with discrete (integer) numbers, you can simulate fractional values using integer numbers. We'll use our same fictional computer system that can only store base-10 numbers in an 8-character space.
We understand how to represent the values 0 through 100 million in that space, because we already covered that above, but how do we represent the fractional value 1/2 without using the weird tricks we used for floating point numbers? With our floating point number system we sacrificed precision in order to gain magnitude; with fixed-point numbers we sacrifice magnitude so that we can keep our precision and gain fractions. In our floating point number system we pretended that there was an invisible decimal point just to the left of our precision digits; with fixed-point numbers we choose a place between two digits and imagine that there's a decimal point there.
Depending on how big of a number we need to represent, we can adjust where the decimal point goes. If, for instance, we're dealing with trigonometric functions and we need PI and values between 0 and +1, then we can imagine that the decimal point is just to the right of the most significant digit. We would store PI as 3141 5927 or e as 2718 2818. We have no way of tracking where the decimal point really is so we can't mix numbers that use a decimal after the first digit with numbers that use a decimal between the 4th and 5th digits. Besides not being able to mix where the decimal point is, the only thing that causes problems is adjusting where the decimal point should be after performing math.
If we use a fixed-point system with the decimal point between the 4th and 5th digits, then PI is represented as 0003 1416. Two times PI can be accomplished using simple integer addition as PI + PI (0003 1416 + 0003 1416 = 0006 2832), but what happens when we multiply PI (0003 1416) by 2 (0002 0000)? Using normal integer multiplication we end up with 628320000, which is too big to fit in our 8-character storage space. The simplest fix is to shift the digits to the right before multiplying--0003 1416 x 0002 0000 becomes 0000 0314 x 0000 0200 = 0006 2800. As you can see, we've just lost two digits of precision, so while this method does work, it doesn't work well. There are methods to overcome this that are still much faster than using floating point, so systems that do not implement floating point numbers can perform multiplication without losing precision.
Summary
Hopefully, that explanation gives you a better understanding of how the two number systems work and relative strengths and weaknesses of each. Of course a real computer system does this all in binary instead of base-10, but the concepts are the same. There are also some reserved patterns of bits in the floating point number system to represent +infinity, -infinity, the very smallest values on either side of 0, and NaN. Fixed-point numbers do not have any reserved patterns, and typically use the most significant bit to represent positive or negative values, just like regular integers.
Java, 80 79 59 57 bytes
s->s.compareTo(new java.math.BigDecimal(new Float(s))+"")
Outputs a negative integer if the internal floating point value is larger; 0 if they're the same; and a positive integer if the floating point is smaller.
Try it online.
Explanation:
s-> // Method with String parameter and integer return
s.compareTo( // Compare the input to (resulting in neg/0/pos):
new java.math.BigDecimal( // Create a BigDecimal, with value:
new Float(s)) // The (32-bit) floating point number of the input
+"") // Convert that BigDecimal to a String
Minor note: I've used Float (which is 32 bits) and therefore holds slightly different values than in the challenge description. If I would change it to Double (which is 64 bits) the values would be the same as the challenge description. This difference can for certain inputs also result in different outputs (e.g. the "0.09" is 0.0900000035762786865234375 as float, resulting in -23, but 0.0899999999999999966693309261245303787291049957275390625 as double, resulting in 1). The overall functionality would still be the same, though:
Try it online.
Vyxal 3, 1 byte
1
Vyxal It Online!
Outputs 1 if equivalent, 0 if bigger, -1 if smaller. This probably polyglots a lot of languages lol. Leave a comment if you have a polyglot language where the answer is always 1.
Notably, this always outputs 1. That's because all numbers are stored exactly by default. That's an intentional language design decision we went out of our way to accommodate by using a third party library under the hood. Technically, the number type is called VNum which extends spire.Complex[Real], so not a "decimal" type but a type that happens to also be able to store decimals exactly by consequence of also storing things like surds exactly.
There's a slight chance this could be considered invalid by the "don't use decimal type" restriction, but that's up to the challenge asker as to whether the default (and only) generic number type being exact is allowed.
Can somebody, in simple terms, explain the difference between the three datatypes in SQL? Thank you