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. Thanks to all for help. Werner 2016-12-01 2:53 GMT+01:00 Keith Medcalf <kmedc...@dessus.com>: > > Wattage problem based on incoherent understanding of how floating point > numbers are stored. > > Not an actual problem if you do your comparisons properly: > > SQLite version 3.16.0 2016-11-30 05:08:59 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> CREATE TABLE transactions (amount REAL); > sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); > sqlite> INSERT INTO transactions VALUES (-0.3); > sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions) > ...> THEN 'zero' > ...> ELSE 'not zero' > ...> END; > zero > sqlite> > sqlite> SELECT sum(amount) FROM transactions; > 2.77555756156289e-17 > sqlite> > > Note that if you do not have a function that does floating-point comparisons > properly, you can always do something like this: > > sqlite> CREATE TABLE transactions (amount REAL); > sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); > sqlite> INSERT INTO transactions VALUES (-0.3); > sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) < 0.005 > ...> THEN 'zero' > ...> ELSE 'not zero' > ...> END; > zero > sqlite> > sqlite> SELECT sum(amount) FROM transactions; > 2.77555756156289e-17 > > The representational limit of 0 is: > > sqlite> select ulp(0); > 1.11022302462516e-16 > > which is this far from the sum(amount) > > sqlite> select ulps(0, sum(amount)) from transactions; > -0.25 > > Note that the IEEE754 value of sum(amount) is less than 1 ulp from 0.0 (1/4 > ULP in my case, 1/2 ULP in your case). > > >> -----Original Message----- >> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] >> On Behalf Of Simon Slavin >> Sent: Wednesday, 30 November, 2016 18:27 >> To: SQLite mailing list >> Subject: Re: [sqlite] Datatype for prices (1,500) >> >> >> On 30 Nov 2016, at 10:43pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> >> >> You were given a good recommendation save everything in "cents". Which >> >> might also be a good solution depending on the underlying language you >> >> use. as you can't store money in a float! >> > >> > And why can you not store money in a float? >> >> Because this: >> >> SQLite version 3.14.0 2016-07-26 15:17:14 >> Enter ".help" for usage hints. >> sqlite> CREATE TABLE transactions (amount REAL); >> sqlite> INSERT INTO transactions VALUES (0.1),(0.1),(0.1); >> sqlite> INSERT INTO transactions VALUES (-0.3); >> sqlite> SELECT CASE WHEN (SELECT sum(amount) FROM transactions) = 0 >> THEN 'zero' >> ELSE 'not zero' >> END; >> not zero >> sqlite> SELECT sum(amount) FROM transactions; >> 5.55111512312578e-17 >> sqlite> >> >> Please note that this is not just a problem with SQLite. One can >> demonstrate the equivalent problem in many programming languages and >> databases. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
