Re: [sqlite] Datatype for prices (1,500)

[Barry Smith]

*argv)
> {
>double x = 0.0;
>double y = 0.0;
>double ulps = 0.0;
>double delta = 5.0;
>uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>if ((argc == 0) || (argc > 3))
>return;
>if (argc == 1)
>y = sqlite3_value_double(argv[0]);
>else
>{
>x = sqlite3_value_double(argv[0]);
>y = sqlite3_value_double(argv[1]);
>}
>if (argc > 2)
>delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
>ulps = distance(x, y);
>if (   ((flag & 2)  && (sqlfcmp_fabs(ulps) <= delta))   /* flag & 2 means 
> test equal */
>|| ((flag & 1)  && (ulps < -delta)) /* flag & 1 means 
> test less than */
>|| ((flag & 4)  && (ulps > delta))  /* flag & 4 means 
> test greater than */
>|| ((flag == 0) && (sqlfcmp_fabs(ulps) > delta)))   /* no flags test 
> not equal */
>sqlite3_result_int(context, 1);
>else
>sqlite3_result_int(context, 0);
> }
> 
> /*
> ** If compiling as a builtin extension, don't export the initializer -- make 
> it SQLITE_PRIVATE
> ** Change name of initializer to sqlite3_init_
> */
> 
> /* SQLite invokes this routine once when it loads the extension.
> ** Create new functions, collating sequences, and virtual table
> ** modules here.  This is usually the only exported symbol in
> ** the shared library.
> */
> 
> #ifdef _WIN32
> #ifndef SQLITE_CORE
> __declspec(dllexport)
> #endif
> #endif
> #ifdef SQLITE_CORE
> static
> #endif
> int sqlite3_sqlfcmp_init(sqlite3 *db, char **pzErrMsg, const 
> sqlite3_api_routines *pApi)
> {
>int nErr = 0;
> 
>SQLITE_EXTENSION_INIT2(pApi);
> 
>nErr += sqlite3_create_function(db, "ulp",  1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulp,   0, 0);
>nErr += sqlite3_create_function(db, "epsilon",  1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulp,   0, 0);
>nErr += sqlite3_create_function(db, "ulps",-1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulps,  0, 0);
>nErr += sqlite3_create_function(db, "fne", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _fpc,   0, 0);
>nErr += sqlite3_create_function(db, "flt", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)1,   _fpc,   0, 0);
>nErr += sqlite3_create_function(db, "feq", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)2,   _fpc,   0, 0);
>nErr += sqlite3_create_function(db, "fle", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)3,   _fpc,   0, 0);
>nErr += sqlite3_create_function(db, "fgt", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)4,   _fpc,   0, 0);
>nErr += sqlite3_create_function(db, "fge", -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)6,   _fpc,   0, 0);
> 
>return nErr ? SQLITE_ERROR : SQLITE_OK;
> }
> 
> #ifdef __cplusplus
> }
> #endif
> 
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Warren Young
>> Sent: Friday, 2 December, 2016 11:37
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Datatype for prices (1,500)
>> 
>>> On Nov 30, 2016, at 6:53 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>>> 
>>> sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)
>> 
>> What’s feq()?  I don’t see it in the SQLite documentation.
>> 
>> ___
>> 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

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

   sqlite3_result_int(context, 1);
else
sqlite3_result_int(context, 0);
}

/*
** If compiling as a builtin extension, don't export the initializer -- make it 
SQLITE_PRIVATE
** Change name of initializer to sqlite3_init_
*/

/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here.  This is usually the only exported symbol in
** the shared library.
*/

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
#ifdef SQLITE_CORE
static
#endif
int sqlite3_sqlfcmp_init(sqlite3 *db, char **pzErrMsg, const 
sqlite3_api_routines *pApi)
{
int nErr = 0;

SQLITE_EXTENSION_INIT2(pApi);

nErr += sqlite3_create_function(db, "ulp",  1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulp,   0, 0);
nErr += sqlite3_create_function(db, "epsilon",  1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulp,   0, 0);
nErr += sqlite3_create_function(db, "ulps",-1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _ulps,  0, 0);
nErr += sqlite3_create_function(db, "fne", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  0,  _fpc,   0, 0);
nErr += sqlite3_create_function(db, "flt", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)1,   _fpc,   0, 0);
nErr += sqlite3_create_function(db, "feq", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)2,   _fpc,   0, 0);
nErr += sqlite3_create_function(db, "fle", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)3,   _fpc,   0, 0);
nErr += sqlite3_create_function(db, "fgt", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)4,   _fpc,   0, 0);
nErr += sqlite3_create_function(db, "fge", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)6,   _fpc,   0, 0);

return nErr ? SQLITE_ERROR : SQLITE_OK;
}

#ifdef __cplusplus
}
#endif


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Warren Young
> Sent: Friday, 2 December, 2016 11:37
> To: SQLite mailing list
> Subject: Re: [sqlite] Datatype for prices (1,500)
> 
> On Nov 30, 2016, at 6:53 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> >
> > sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)
> 
> What’s feq()?  I don’t see it in the SQLite documentation.
> 
> ___
> 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

Re: [sqlite] Datatype for prices (1,500)

[Warren Young]

On Nov 30, 2016, at 6:53 PM, Keith Medcalf  wrote:
> 
> sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)

What’s feq()?  I don’t see it in the SQLite documentation.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Chris Locke]

PHP will easily display a value with trailing zeros - you don't add '00'
programmatically.

eg:   $number = number_format(1234, 2, '.', '');

On Thu, Dec 1, 2016 at 8: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.
>
> Thanks to all for help.
>
> Werner
>
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Stephen Chrzanowski]

Werner ;

In whatever language you use, how do you store a number?  How do you
display that number?  How do you take that set of bytes at a particular
memory location and get it to the users eyeballs?  You have to format it.
Your software converts that list of bytes into something human readable,
because, just looking at #3fc0, I'd have NO clue what that means.

Any language I've used, if you have a float type, and output, just that raw
number, it'll output the minimal, formatted *human readable *number, unless
you use some kind of formatting routine.  var a:real;A:=1.5;writeln(A);
--- Outputs 1.5.

SQLite stores any float/real/decimal number/integer as bytes in a row.  You
won't find "1.5" or "1.500" if you look at the SQLite file in a hex editor
unless you specifically stored it as text, but even then that might not be
true, because the DB engine might be intelligent enough to say "Hey!  This
is a number!  I'll store it as 4 bytes."  SQLite3, will quite literally in
that case, store the number as 4 bytes instead of 6-12 bytes for the
ASCII/UNICODE text version.

#3fc0 is what 1.5 or 1.500 or 1.500 may look
like stored in the database. (That hex number grabbed from
https://www.h-schmidt.net/FloatConverter/IEEE754.html )

From *ANY* tool, what comes OUT of the database to your screen or printer
is a REPRESENTATION of what you want to see.  Its a User Interface thing.
Its making the number "Look Pretty".  Its 100% Aesthetic, for looks,
EXACTLY nothing more, and EXACTLY nothing less.

Whatever you are using, be it the MySQL client, or PHP, or Java, or QBASIC
or whatever you use, when you go and look at that number and that 'tool'
shows you that number, there are a LOT of things that are going on in the
background to show you what #3fc0 is.  In MySQLs case, it apparently
looks at the definition of the column, sees that you've defined it as
something, then will PRESENT you with something that LOOKS like what it is
defined as.  It does NOT change how the number is stored, but just how you
want the output.  Its a superficial thing.

When you do calculations, say, 1.5*1.3, the computer is doing the float
math against 8 bytes, not "1.5" and "1.3".  (Float math breaks my brain,
like Pentiums F-Div bug)

On Fri, Dec 2, 2016 at 7:49 AM, Dominique Devienne 
wrote:

> On Fri, Dec 2, 2016 at 1:01 PM, Werner Kleiner 
> wrote:
>
> > @Darren,
> >
> > my "problem" is, that the both trailing zeros 00 after 1.5 are
> > missing in sqlite. In MySQL the price is stored as 1.500
> >
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Dominique Devienne]

On Fri, Dec 2, 2016 at 1:01 PM, Werner Kleiner 
wrote:

> @Darren,
>
> my "problem" is, that the both trailing zeros 00 after 1.5 are
> missing in sqlite. In MySQL the price is stored as 1.500
>

Do the printf transparently via a view in front of your table? --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (price number);
sqlite> insert into t values (1.5), (0.1), (2.2);
sqlite> create view v as select printf('%7.3f', price) as price from t;
sqlite> select * from v;
  1.500
  0.100
  2.200
sqlite> select typeof(price), price from t;
real|1.5
real|0.1
real|2.2
sqlite> select typeof(price), price from v;
text|  1.500
text|  0.100
text|  2.200
sqlite> drop view v;
sqlite> create view v as select printf('%.3f', price) as price from t;
sqlite> select typeof(price), price from v;
text|1.500
text|0.100
text|2.200
sqlite> select sum(price) from v;
3.8
sqlite> select sum(price) from t;
3.8
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

@Darren,

my "problem" is, that the both trailing zeros 00 after 1.5 are
missing in sqlite
In MySQL the price is stored as 1.500

I do not undestand exact what you mean with multiply the number?

If I multiply 1.5 x 1 with PHP before storing in sqlite then I get 15000?

best regards
Werner

2016-12-01 11:46 GMT+01:00 Darren Duncan :
> Look, you want to store the same level of detail that a decimal(7,4) does?
>
> Easy, you just multiply the conceptual number by 10,000 and it represents
> hundredths of a cent, the exact same precision you are using in MySQL.
>
> Your examples would then be stored as 20 or 8 respectively.  And every other
> possible value you could store in the MySQL you can now store in SQLite,
> consistently.
>
> -- Darren Duncan
>
> On 2016-12-01 12: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.
>>
>> Thanks to all for help.
>
>
> ___
> 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

Re: [sqlite] Datatype for prices (1,500)

[R Smith]

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

Re: [sqlite] Datatype for prices (1,500)

[Simon Slavin]

On 2 Dec 2016, at 2:30am, James K. Lowden  wrote:

> Jens Alfke  wrote:
> 
>> I understand that the consensus among those who do is to _not_ store
>> monetary values as floating-point, due to the roundoff error. 
> 
> I wouldn't be so sure.  I've worked in that space for 30 years designing
> databases and applications.  I don't remember once representing money as
> anything but floating point, except on mainframes.  Occasionally we
> used exact-decimal database types for bulk-load tables, to verify the
> vendor's promised 18,6 scaling factor.   But in production?  For
> computation?  Floats, always and ever.

Floating point is fine for science.  And for calculations in banking which 
inherently deal with non-integers (e.g. compound interest).  But once you’ve 
finished your compound interest calculation you are going to round the result 
in exactly the way you were told to, and you will store it as an amount of 
money which you can exactly hand over to the account-holder in cash.

To do anything else would mean that any audit required endless tedious 
calculations to prove you weren’t adding up fractions to sneak our an odd pound 
or penny.  Plus endless checking to see that no amount corresponding to a 
transaction was a fractional unit of currency.

(The fact that some prices and exchange rates manipulate currencies as 
"percentage in point", meaning that a dollar is 1 "pips" not 100 "cents", 
is considered unhelpful in the extreme to my point !)

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

On Thursday, 1 December, 2016 19:50, Jens Alfke  said:

> > On Nov 30, 2016, at 5:53 PM, Keith Medcalf  wrote:

> > Wattage problem based on incoherent understanding of how floating point
> > numbers are stored.

> You may not be aware of this, Keith, but that comes off as really snarky
> and condescending.

Factual information often does when it is contrary to something that has been 
deliberately oversimplified for mass consumption to the point of being 
incorrect.

> I feel I have a fairly coherent (if not domain-expert) understanding of
> FP, and it sounds like others here do too. And while I do not myself work
> with database schema involving money, I understand that the consensus
> among those who do is to _not_ store monetary values as floating-point,
> due to the roundoff error. Some people reject algorithms out-of-hand that
> accumulate even tiny amounts of roundoff error*. And of course the
> roundoff error increases as the size of the integer portion of the number
> increases.

That ancient recommendation is from the good old days when computers used what 
is now called "fast floating point" which was designed to have about a digit 
more accuracy than a slide-rule -- and performed computations with about the 
same speed as a slide-rule.  

In IEEE754 that is called binary16 (or half precision) and has an accuracy of 3 
decimal digits -- completely unsuitable for money.  binary32 (single precision) 
has an accuracy of about 7 decimal digits and is good as long as no operand, 
intermediate, or result exceeds about $100.  binary64 (double precision) has an 
accuracy of about 15 decimal digits and is good as long as no operand, 
intermediate, or result is greater than about $1,000,000,000.  binary128 
(quadruple precision) has an accuracy of about 33 decimal digits and is good up 
to $10^28.  Binary256 (octuple precision) has an accuracy of about 71 decimal 
digits and is good up to $10^67.  Decimal32, Decimal64 and Decimal128 have 
about the same precision as their binary counterparts (as do the various fixed 
point BCD or packed-decimal formats, or scaled integer binary types, or even 
bignums).  Fixed or Floating arithmetic is equally accurate within the limits 
of precision associated with the storage and calculation format in use.

Keith
 
> —Jens
> 
> * just as some people reject UUID schemes with a tiny-but-nonzero chance
> of collisions, hmm?

Not really.  Using a storage format that has the required precision cannot lead 
to "happenstance" (probabilistic) errors, whether that storage format is fixed 
or floating point, binary, decimal, BCD, packed-decimal, or scaled integer.  
However, UUID schemes have a 100% certainty of collision.  There is a huge 
difference.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[James K. Lowden]

On Thu, 1 Dec 2016 16:50:21 -0800
Jens Alfke  wrote:

> I understand that the consensus among those who do is to _not_ store
> monetary values as floating-point, due to the roundoff error. 

I wouldn't be so sure.  I've worked in that space for 30 years designing
databases and applications.  I don't remember once representing money as
anything but floating point, except on mainframes.  Occasionally we
used exact-decimal database types for bulk-load tables, to verify the
vendor's promised 18,6 scaling factor.   But in production?  For
computation?  Floats, always and ever.  

I have seen quite a few discussions here suggesting integers instead
because they're exact.  To me, that sounds like nothing so much as
Charles Moore.  He made a virtue of necessity: since Forth had no
floating point, he recommended using integers, and keeping track fo the
decimal place yourself.  

The difficulty in using floating point for money is *not* accuracy.
It's equivalency.  The first-year programmer soon learns not to test
for equivalency using "=", but by the roundabout process of comparing
the absolute difference to an epsilon, usually about 1E-6.  

What's much more problematic is keeping anything, including money, in a
nonstandard format, and introducing bespoke logic to manage the decimal
place.  Opportunities abound for error and misunderstanding, and
instead of being off by a penny, you're off by 10 or 100 times.
Percentages were a great one for that.  Pounds and pence in Ireland,
too. 

Not to put words in Keith's mouth, I think his point is that if you're
surprised about floating point behavior in SQLite, you haven't mastered
your trade.  It's not a database problem; it's a question of
understanding how IEEE floating point works.  The decision to use
integers or strings instead will only yield different, and worse,
problems.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Jens Alfke]

> On Nov 30, 2016, at 5:53 PM, Keith Medcalf  wrote:
> 
> Wattage problem based on incoherent understanding of how floating point 
> numbers are stored.  

You may not be aware of this, Keith, but that comes off as really snarky and 
condescending.

I feel I have a fairly coherent (if not domain-expert) understanding of FP, and 
it sounds like others here do too. And while I do not myself work with database 
schema involving money, I understand that the consensus among those who do is 
to _not_ store monetary values as floating-point, due to the roundoff error. 
Some people reject algorithms out-of-hand that accumulate even tiny amounts of 
roundoff error*. And of course the roundoff error increases as the size of the 
integer portion of the number increases.

—Jens

* just as some people reject UUID schemes with a tiny-but-nonzero chance of 
collisions, hmm?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Darren Duncan]

Look, you want to store the same level of detail that a decimal(7,4) does?

Easy, you just multiply the conceptual number by 10,000 and it represents 
hundredths of a cent, the exact same precision you are using in MySQL.


Your examples would then be stored as 20 or 8 respectively.  And every other 
possible value you could store in the MySQL you can now store in SQLite, 
consistently.


-- Darren Duncan

On 2016-12-01 12: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.

Thanks to all for help.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

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

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

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

Re: [sqlite] Datatype for prices (1,500)

[Ryan Noll (Mailing List)]

On 11/30/2016 11:42 AM, Richard Hipp wrote:

On 11/30/16, Chris Locke  wrote:

I recently had this problem. Values stored as real values. Had to check
records in the database to see if any value had changed, and needed
updating. Even though all values in my code were singles, I had bad
rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
database. Aargh.
Storing as integers is the way to go.

Just to be clear, this is a property of binary floating-point numbers,
not a quirk of SQLite.

Using the IEEE 64-bit floating point format, there is no way to
represent values 0.1, 2.2, and 2.3.  The closest you can get are the
following:

0.1: 0.155511151231257827021181583404541015625

2.2: 2.20017763568394002504646778106689453125

2.3: 2.29982236431605997495353221893310546875

If you add the first numbers you get:

2.300266453525910037569701671600341796875

which is not equal to the third number.  Points to remember:

(1) Floating point numbers are usually approximations, not exact values.

(2) Never compare two floating point numbers for equality



Excellent example. I remember when I was taking a digital fundamentals 
course, one of the exercise problems was to show the basic binary 
representation of 0.1 (decimal) using in a "flat binary" number. (For 
example, 0.1 binary would be 0.5 decimal--half, 0.01 binary would be 
0.25 decimal, etc.) So, I set out to find 0.1 by this method. (This was 
a long time ago.) I found that by the 10th binary decimal place, I was 
getting tired of attempting this problem. I was young and dumb at the 
time, so the question was asked the next day in class and it was 
described as a property of binary floating point concepts. This also 
lead to a discussion of binary coded decimal.


The only thing I would add to point #2 (or maybe add it as point #3) is 
if you do need to compare floating point numbers make sure to use some 
sort of tolerance value that is acceptable.


Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Darren Duncan]

On 2016-11-30 3:14 PM, Keith Medcalf wrote:



On Wednesday, 30 November, 2016 17:58, Darren Duncan  
said:


On 2016-11-30 2:43 PM, Keith Medcalf 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?


Unlike say measured data like temperature or rainfall, which are
inherently
inexact, money is not measured and is considered exact information, at
least in
typical cases of recording transactions, so needs to be represented in an
exact
format.  You don't want your bank balance to be changing by different
values
than the exact amount you insert or withdraw, do you? -- Darren Duncan


I do not follow.

There is no reason that money cannot be stored as IEEE754 floating point 
(32-bit format lesser formats such as fast-float are more problematic than 
64-bit or longer binary, but can easily handle more than 10 times all the money 
every created), and it can certainly be stored in IEEE854 floating point.

Why exactly do you think there is a problem?  Or is it really just a generally 
observed wattage problem (in the understanding of numbers and arithmetic and 
how computers work).


The problem is that IEEE floats are inexact.  Even with 64-bit versions, summing 
a lot of money values is eventually going to create rounding errors when 
conceptually summing should always produce exact results, which we care about 
when dealing with money. -- Darren Duncan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Simon Slavin]

On 30 Nov 2016, at 10:43pm, Keith Medcalf  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

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

On Wednesday, 30 November, 2016 17:58, Darren Duncan  
said:

> On 2016-11-30 2:43 PM, Keith Medcalf 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?
> 
> Unlike say measured data like temperature or rainfall, which are
> inherently
> inexact, money is not measured and is considered exact information, at
> least in
> typical cases of recording transactions, so needs to be represented in an
> exact
> format.  You don't want your bank balance to be changing by different
> values
> than the exact amount you insert or withdraw, do you? -- Darren Duncan

I do not follow.  

There is no reason that money cannot be stored as IEEE754 floating point 
(32-bit format lesser formats such as fast-float are more problematic than 
64-bit or longer binary, but can easily handle more than 10 times all the money 
every created), and it can certainly be stored in IEEE854 floating point.

Why exactly do you think there is a problem?  Or is it really just a generally 
observed wattage problem (in the understanding of numbers and arithmetic and 
how computers work).





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Doug Currie]

On Wed, Nov 30, 2016 at 5:25 PM, Nico Williams 
wrote:

> On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote:
> >
> > [...] every RDMS "should" implement Decimal Floating Point.
>
> You could argue that every programming language needs that.  What makes
> SQL more special than the others in this regard?
>

The SQL standard (at least SQL92) specifies an exact numeric type that uses
decimal precision for rounding. Most other programming languages don't.

There are reasons we use IEEE754: it's fixed-sized, it's built-in pretty
> much everywhere, and it's usually implemented in hardware, so it's fast.


The IEEE754-2008 standard includes both base 2 (binary) and base 10
(decimal) numbers. E.g., decimal64, decimal128, as well as binary64
(typical C double) and binary32 (typical C float). There are few hardware
implementations of decimal floats; modern processors that have it include
IBM System Z and POWER6.

e
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Darren Duncan]

On 2016-11-30 2:43 PM, Keith Medcalf 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?


Unlike say measured data like temperature or rainfall, which are inherently 
inexact, money is not measured and is considered exact information, at least in 
typical cases of recording transactions, so needs to be represented in an exact 
format.  You don't want your bank balance to be changing by different values 
than the exact amount you insert or withdraw, do you? -- Darren Duncan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

> > I recently had this problem. Values stored as real values. Had to check
> > records in the database to see if any value had changed, and needed
> > updating. Even though all values in my code were singles, I had bad
> > rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
> > database. Aargh.

The distance between the IEEE745 representation of 0.1 + the IEEE754 
representation of 2.2 is within 1 ULP of IEEE754 representation of 2.3 as is 
prescribed by the IEEE754 specifications.  On this scale 1 ULP is 
4.44089209850063e-16 which means the difference is 
0.0004408920950063





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Keith Medcalf]

> 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?





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Nico Williams]

On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote:
> On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke  wrote:
> > Storing as integers is the way to go.
> 
> At present, I think that is the best way. Of course, we could argue that
> every RDMS "should" implement Decimal Floating Point.

You could argue that every programming language needs that.  What makes
SQL more special than the others in this regard?

There are reasons we use IEEE754: it's fixed-sized, it's built-in pretty
much everywhere, and it's usually implemented in hardware, so it's fast.
Those are fantastic selling points for IEEE754.  There are reasons we
all dislike it too...

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[John McKown]

On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke  wrote:

> I recently had this problem. Values stored as real values. Had to check
> records in the database to see if any value had changed, and needed
> updating. Even though all values in my code were singles, I had bad
> rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
> database. Aargh.
> Storing as integers is the way to go.
>

​At present, I think that is the best way.​ Of course, we could argue that
every RDMS "should" implement Decimal Floating Point.
https://en.wikipedia.org/wiki/Decimal_floating_point



>
> Thanks,
> Chris
>

-- 
Heisenberg may have been here.

Unicode: http://xkcd.com/1726/

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Richard Hipp]

On 11/30/16, Chris Locke  wrote:
> I recently had this problem. Values stored as real values. Had to check
> records in the database to see if any value had changed, and needed
> updating. Even though all values in my code were singles, I had bad
> rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
> database. Aargh.
> Storing as integers is the way to go.

Just to be clear, this is a property of binary floating-point numbers,
not a quirk of SQLite.

Using the IEEE 64-bit floating point format, there is no way to
represent values 0.1, 2.2, and 2.3.  The closest you can get are the
following:

0.1: 0.155511151231257827021181583404541015625

2.2: 2.20017763568394002504646778106689453125

2.3: 2.29982236431605997495353221893310546875

If you add the first numbers you get:

   2.300266453525910037569701671600341796875

which is not equal to the third number.  Points to remember:

(1) Floating point numbers are usually approximations, not exact values.

(2) Never compare two floating point numbers for equality


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Chris Locke]

I recently had this problem. Values stored as real values. Had to check
records in the database to see if any value had changed, and needed
updating. Even though all values in my code were singles, I had bad
rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the
database. Aargh.
Storing as integers is the way to go.

Thanks,
Chris
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Nico Williams]

On Wed, Nov 30, 2016 at 04:39:25PM +0100, Werner Kleiner wrote:
> yes thats right, but it would be nice to have same behavior between
> MySQL and sqlite

SQLite3 has very few types.  Adding types is very difficult, so I doubt
new types will be added anytime soon.

Price data and datetime data are the ones most commonly requested.
SQLite3 does have suitable types for representing them, but not quite
the native types that you and others want.  It's a fact you must accept
or else not use SQLite3.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[David Raymond]

Storing as either an integer or real will let you do math on the returned 
value. If you're looking for a pretty representation of the number, then that 
should be done in your own application.

Alternatively though, you can explicitly query for the formatted text version 
of your number using the printf() function.

Integer:
select printf('.03f', 1700 / 1000.0); returns the text string '1.700' (The .0 
on the 1000.0 is important by the way)

Real:
select printf('.03f', 1.7); returns the text string '1.700'

So when getting the number for math just use
select basicprice from prices;

And when you're printing a pretty representation to the screen use either
select printf('.03f', basicprice) from prices;--if real
or
select printf('.03f', basicprice / 1000.0) from prices;--if integer

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, November 30, 2016 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Datatype for prices (1,500)


On 30 Nov 2016, at 3:52pm, Werner Kleiner <sqlitetes...@gmail.com> wrote:

> < 
> I tried this, but Inserting 1.700 is also stored as 1.7
> 
> With TEXT I can store it like 1.700 but we want to calculate later
> with PHP or C#

If what you want is calculation-ready value, then storing it as REAL=1.7 is 
correct.

If what you want is a print-ready value, then storing it as TEXT='1.700' is 
correct.

There is no type which is good for both things.

Since most print routines already have formatting options which allow you to 
specify the number of decimal points, it’s more convenient to store as a number 
and worry about the text format only when you’re printing.

On the other hand, if you value the accuracy of a money calculation very 
highly, then you should be storing an integer, not a REAL value.  This means 
you do not run the risk of storing a value which is not an exact amount of 
money.

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

Re: [sqlite] Datatype for prices (1,500)

[Simon Slavin]

On 30 Nov 2016, at 3:52pm, Werner Kleiner  wrote:

> < 
> I tried this, but Inserting 1.700 is also stored as 1.7
> 
> With TEXT I can store it like 1.700 but we want to calculate later
> with PHP or C#

If what you want is calculation-ready value, then storing it as REAL=1.7 is 
correct.

If what you want is a print-ready value, then storing it as TEXT='1.700' is 
correct.

There is no type which is good for both things.

Since most print routines already have formatting options which allow you to 
specify the number of decimal points, it’s more convenient to store as a number 
and worry about the text format only when you’re printing.

On the other hand, if you value the accuracy of a money calculation very 
highly, then you should be storing an integer, not a REAL value.  This means 
you do not run the risk of storing a value which is not an exact amount of 
money.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

@Simon

<:
> << All you need to store in the database table is the number. How it is
> formatted for viewing or printing is controlled by the user interface.
>
> yes thats right, but it would be nice to have same behavior between
> MySQL and sqlite
> I think to use a char datatype is not a good solution.
>
> 2016-11-30 16:27 GMT+01:00 Rich Shepard :
>> On Wed, 30 Nov 2016, Igor Tandetnik wrote:
>>
>>> Store it as an integer, in $.0001 units. So $1.500 would be represented
>>> simply as an integer 1500.
>>
>>
>>   All you need to store in the database table is the number. How it is
>> formatted for viewing or printing is controlled by the user interface.
>>
>> Rich
>>
>> ___
>> 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

Re: [sqlite] Datatype for prices (1,500)

[Stephen Chrzanowski]

Werner;

The data is stored as raw as it gets.  What you use to physically SEE that
data is presented by 'whatever it is you use'.  So SQLite3.exe is going to
present it one way, as close to as raw format as possible, in as
minimalistic  , while tools like SQLite Expert looks at the value types and
decides itself how to present the data.  The same deal is with MySQL.  It
presents the raw data to as raw as it gets.  The fact that you specify
decimal(7,4) tells the MySQL engine that the output should kinda look a
certain way.  It doesn't store the additional zeros, it presents them to
you that way.  It'll actually store the additional numbers beyond the 4th
decimal number, you just aren't given that.

What you're asking from the database is purely presentation and is entirely
based on whatever tool you're using to call up the data.  The tool you use
is going to be the deciding factor on how its displayed.

On top of that, SQLite3 isn't based on MySQL.  So you're going to see
different commands, logic and output.

On Wed, Nov 30, 2016 at 10:39 AM, Werner Kleiner 
wrote:

> << All you need to store in the database table is the number. How it is
> formatted for viewing or printing is controlled by the user interface.
>
> yes thats right, but it would be nice to have same behavior between
> MySQL and sqlite
> I think to use a char datatype is not a good solution.
>
> 2016-11-30 16:27 GMT+01:00 Rich Shepard :
> > On Wed, 30 Nov 2016, Igor Tandetnik wrote:
> >
> >> Store it as an integer, in $.0001 units. So $1.500 would be represented
> >> simply as an integer 1500.
> >
> >
> >   All you need to store in the database table is the number. How it is
> > formatted for viewing or printing is controlled by the user interface.
> >
> > Rich
> >
> > ___
> > 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

Re: [sqlite] Datatype for prices (1,500)

[Miha Vrhovnik]

On 2016-11-30 16:39, Werner Kleiner wrote:

<< All you need to store in the database table is the number. How it is
formatted for viewing or printing is controlled by the user interface.

yes thats right, but it would be nice to have same behavior between
MySQL and sqlite
I think to use a char datatype is not a good solution.

No using a char datatype is not a solution AS it is not a solution for 
SQLite to implement everything in a way that's compatible with database 
X.


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!


BR,
Miha
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Simon Slavin]

On 30 Nov 2016, at 3:26pm, Werner Kleiner  wrote:

> << 
> In sqlite studio you can configure a column a decimal(7,4) .
> but anyway, how can you store a price like 1.500 with numeric type?



Declare it as REAL.

However, if you’re handling an amount you think of as rounded to three places, 
then you should probably be storing INTEGERs, and the database should hold your 
amounts multiplied by a thousand.  This gets rid of rounding problems if you 
expect SQLite to do any mathematics with your values (e.g. total() or ORDER BY).

If all you want from SQLite is to store and retrieve your values you could 
store them as TEXT without problems, and that way you can have commas in them.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

<< All you need to store in the database table is the number. How it is
formatted for viewing or printing is controlled by the user interface.

yes thats right, but it would be nice to have same behavior between
MySQL and sqlite
I think to use a char datatype is not a good solution.

2016-11-30 16:27 GMT+01:00 Rich Shepard :
> On Wed, 30 Nov 2016, Igor Tandetnik wrote:
>
>> Store it as an integer, in $.0001 units. So $1.500 would be represented
>> simply as an integer 1500.
>
>
>   All you need to store in the database table is the number. How it is
> formatted for viewing or printing is controlled by the user interface.
>
> Rich
>
> ___
> 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

Re: [sqlite] Datatype for prices (1,500)

[Rich Shepard]

On Wed, 30 Nov 2016, Igor Tandetnik wrote:


Store it as an integer, in $.0001 units. So $1.500 would be represented
simply as an integer 1500.


  All you need to store in the database table is the number. How it is
formatted for viewing or printing is controlled by the user interface.

Rich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Werner Kleiner]

<<:
> On 30 Nov 2016 at 14:42, Werner Kleiner  wrote:
>
>> Hello,
>> there is a small application which uses MYSQL db and can also switch to 
>> sqlite
>> In MySQL a table "prices" has a column "basicprice" which is decimal(7,4)
>> I can store a price in form 1.500 there.
>> Same table and same columns in sqlite with datatype float(7,4) stores
>> this value like
>> 1.5
>> It would be nice if there is a way to store in the same way as in
>> MySQL with filling zeros.
>>
>> Is this not possible with sqlite?
>> I also tried type decimal(7,4)
>> Only with 1,500 the zeros are there, but I do not want to store a
>> comma in the db
>
> No such thing as decimal(7,4) in SQLite.
>
> Have a look at:
>
> http://www.sqlite.org/datatype3.html
>
>
> --
> Cheers  --  Tim
> ___
> 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

Re: [sqlite] Datatype for prices (1,500)

[Igor Tandetnik]

On 11/30/2016 9:42 AM, Werner Kleiner wrote:

It would be nice if there is a way to store in the same way as in
MySQL with filling zeros.

Is this not possible with sqlite?


Store it as an integer, in $.0001 units. So $1.500 would be represented 
simply as an integer 1500.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Datatype for prices (1,500)

[Tim Streater]

On 30 Nov 2016 at 14:42, Werner Kleiner  wrote: 

> Hello,
> there is a small application which uses MYSQL db and can also switch to sqlite
> In MySQL a table "prices" has a column "basicprice" which is decimal(7,4)
> I can store a price in form 1.500 there.
> Same table and same columns in sqlite with datatype float(7,4) stores
> this value like
> 1.5
> It would be nice if there is a way to store in the same way as in
> MySQL with filling zeros.
>
> Is this not possible with sqlite?
> I also tried type decimal(7,4)
> Only with 1,500 the zeros are there, but I do not want to store a
> comma in the db

No such thing as decimal(7,4) in SQLite.

Have a look at:

http://www.sqlite.org/datatype3.html


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users