Decimal places problem with SQLite

Question

I have a SQLite3 table with a column having format DECIMAL(7,2), but whenever I select rows with values not having a non-zero 2nd decimal place (eg. 3.00 or 3.10), the result always has trailing zero(s) missing (eg. 3 or 3.1). Is there any way that I can apply a formatting function in the SELECT statement so that I get the required 2dp? I have tried ROUND(), but this has no effect. Otherwise I have to keep converting the resulting column values into the required format for display (using Python in my case) every time I do a SELECT statement, which is a real pain.

I don't even mind if the result is string instead of numeric, as long as it has the right number of decimal places.

Any help would be appreciated.

Alan

Answer 1

SQLite internally uses IEEE binary floating point arithmetic, which truly does not lend itself well to maintaining a particular number of decimals. To get that type of decimal handling would require one of:

1. Fixed point math, or
2. IEEE decimal floating point (rather uncommon), or
3. Handling everything as strings.

Formatting the values (converting from floating point to string) after extraction is the simplest way to implement things. You could even hide that inside some sort of wrapper so that the rest of the code doesn't have to deal with the consequences. But if you're going to do arithmetic on the value afterwards then you're better off not formatting and instead working with the value as returned by the query, because the format and reconvert back to binary floating point (which Python uses, just like the vast majority of other modern languages) loses lots of information in the reduced precision.