I am developing application which should help with gas storage optimization. Storages are implemented as tables in MySQL database, which among others contain injRateAvailable, withdRateAvailable and StorageAvailable (available Volume) columns which again contain values per Timestamp.
Now all those values are 'double' in MySQL and I perform all calculations and comparisons with doubles in Java, and because I was a little lazy I haven't converted all that to BigDecimal when I found out that working with floating point numbers can/will lead to problems in some cases.
That worked so for a while, but now I am encountering problems. I deleted all deals, and StorageAvailable should be back to it's maximum. It was on some places over, because that shows me if some deals are endangered (lack of gas or in case of negative values lack of free volume) in the future.
But as I expected and ignored problems have started to occur. As you see MySQL shows 71280 for StorageAvailable, but query asked for greater then 7180.
If I remove one more null it will return an empty set.
Now I am not sure even if I should change from double to BigDecimal in java, I'm thinking if it would be enough to do some rounding, because I think I don't need so great precision and I think double rounded to three or even two decimal places should be enough. This, considering the lost of performance because of working with BigDecimal and DECIMAL in MySQL, is why I am thinking about rounding. Though I am not sure how big that lost would be, maybe marginal...
Now if anyone could give me a suggestion or an advice on would it be enough to 'convert' calculations in Java to BigDecimal or I should change double to decimal in MySQL also, or could I even end up with rounding?
You should use BigDecimal instead of double. Talking about performance is irrelevant because using double is functionally wrong. Do you want to be the one responsible for intentionally making a decision that produces incorrect output.