On 2016/12/01 10:08 AM, Werner Kleiner wrote:
As I can see storing prices is a topic with different ways and
different solutions.
The advice to store prices in Cent or Integer:
Yes you can do: but how will you sore hundredth cents amounts or tenth
cent prices?
I have prices like 0,0020 or 0,0008 Euro
I think I have to manipulate the prices for viewing in the app with PHP.
Fact is: we can have to DBs MySQL and SQlite. MySQL with decimal(7,4)
stores a price 1.500 from a textfield exact so.
If you want to show the price again in the app, there is nothing to do.
But switching to SQLite the price is viewed as 1.5 (and stored)
I know this is no error of SQLite. But I have now to differ between
Sqlite and MySQL and have to optimize the SELECT and adding 00
programmatically to view correct if using Sqlite.
My original post was if there is a way for both DBs with same
behavior, but it seems not.
To add to the excellent explanations of others, firstly, to store exact
figures you can use Integer and integer divisions or a ULP mechanism of
the sort Keith mentioned, but there is no native data type that has that
in SQLite.
Note that MySQL and others that implement the decimal type do not
actually store values like that, there is no way to store values like
that, they simply do the formatting for you (pre-calculation, which is
handy).
That means they store the values as Integer or Float (or perhaps string
even) and then use internal functions to value/shape it prior to output
or calculation. It makes those engines "Heavier" than SQLite, and adding
it here will kill some of the "Lite" in SQLite.
Best advice we can give is: Use floats shaped with Printf() functions to
display these values, and use a difference epsilon of your comfort level
in comparisons... And if you insist to have the engine do some of that
for you, use MySQL in stead.
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
