Re: [sqlite] Is there a way to select a precision?

[Michael Black]

OK...this way then...it's stored correctly and the view should display
correctly in a 3rd party app.
Correctly gives the 99 cent discount.
Should work for any 2 decimal place number.


create table mine(a number(10,2));
create view v1 as select a/100.0 as a from mine;
create trigger t1 after insert on mine
for each row begin
update mine set a = cast(round(new.a*100) as integer) where
rowid=new.rowid;
end;
create trigger t2 after update on mine
for each row
begin
update mine set a=cast(round(a*100) as integer) where
typeof(a)!='integer' and rowid=new.rowid;
end;
insert into mine values(1.0/3.0);
select * from mine;
select a*3 from v1;
.99
update mine set a=1/3.0;
select a*3 from v1;
.99



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden
Sent: Monday, May 06, 2013 8:03 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a way to select a precision?

On Mon, 6 May 2013 07:42:43 -0500
"Michael Black"  wrote:

> Yes...other databases do respect NUMBER(10,2) on SELECT's.
...
> Can you simply use round to do what you want?
> CREATE TABLE stuff(f number(10,2));
> INSERT INTO "stuff" VALUES(1.0/3.0);
> sqlite> select f from stuff;
> 0.333
> sqlite> select round(f,2) from stuff;
> 0.33

To be clear, Paul van Helden isn't talking about SELECT.  He's talking
about INSERT, about not storing more precision that the input actually
represents.  

Apply a 33% discount to a $3.00 purchase.  Is the bill $2.00, or
$2.01?  If you say $2.00, then apply a 33% discount to three $1
purchases in three separate months.  I imagine you'd agree the total
discount is just 99 cents.  

Whether or not SQLite ought to support exact decimal types is a
separate question; I don't think anyone is saying it should.  But it
isn't just a matter of presentation.  

--jkl




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

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

Re: [sqlite] Is there a way to select a precision?

[James K. Lowden]

On Mon, 6 May 2013 07:42:43 -0500
"Michael Black"  wrote:

> Yes...other databases do respect NUMBER(10,2) on SELECT's.
...
> Can you simply use round to do what you want?
> CREATE TABLE stuff(f number(10,2));
> INSERT INTO "stuff" VALUES(1.0/3.0);
> sqlite> select f from stuff;
> 0.333
> sqlite> select round(f,2) from stuff;
> 0.33

To be clear, Paul van Helden isn't talking about SELECT.  He's talking
about INSERT, about not storing more precision that the input actually
represents.  

Apply a 33% discount to a $3.00 purchase.  Is the bill $2.00, or
$2.01?  If you say $2.00, then apply a 33% discount to three $1
purchases in three separate months.  I imagine you'd agree the total
discount is just 99 cents.  

Whether or not SQLite ought to support exact decimal types is a
separate question; I don't think anyone is saying it should.  But it
isn't just a matter of presentation.  

--jkl




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

Re: [sqlite] Is there a way to select a precision?

[Keith Medcalf]

You could also store the precision in the datatype and the client can retrieve 
the column declaration so that it can fiddle with the data before displaying it.

This is a client issue, not a database issue.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of ven...@intouchmi.com
> Sent: Monday, 06 May, 2013 06:20
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a way to select a precision?
> 
> You could always store the precision info in another column or two.
> 
> Vance
> 
> on May 06, 2013, Paul van Helden  wrote:
> >
> >> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that
> >> SQLite will attempt to store (string) values as integers first and
> floats
> >> second before giving up and storing strings.
> >>
> >
> >This has nothing to do with my reply and I understand how it works.
> >
> >>
> >> You do realize that there are decimal numbers that have infinite binary
> >> expansions?
> >>
> >
> >I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
> >they are the same in SQLite).
> >
> >>
> >> You are also talking presentation (as in formatting) of numeric values
> as
> >> opposed to representation (as in storing/retrieving). The former is
> best
> >> handled in the user interface while the latter is the subject of
> database
> >> engines.
> >>
> >
> >My point was about not storing binary junk - the part of a number that
> has
> >no meaning because the accuracy of the inputs is limited. When you have a
> >generic db manager that can show any table or if you are looking at the
> >results of your own SQL statement, it helps to reduce clutter on the
> >screen. The data also compresses better.
> >
> >>
> >> Fatihful reproduction of formatting would be possible using TEXT
> affinity
> >> and calling sqlite3_bind_text. Performing arithmetic with these
> "numbers"
> >> would however be tricky, slow and would still not guarantee that
> calculated
> >> values would conform to the desired formatting.
> >>
> >> Of course, but in most cases we don't need to go this far. My main
> point
> >is about rounding before binding; my secondary point that scale in a
> column
> >definition can be desirable to avoid it.
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Is there a way to select a precision?

[Simon Slavin]

On 6 May 2013, at 2:10pm, Simon Slavin  wrote:

> You can format them after retrieving them from the database, or you can 
> format them before retrieving them from the database before putting them in 
> the database, in which case you should consider that you're storing strings, 
> not numbers.

Apologies.  That should have read

You can format them after retrieving them from the database, or you can format 
them before putting them in the database, in which case you should consider 
that you're storing strings, not numbers, and be cautious about using them for 
calculations, even summing a column.

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

Re: [sqlite] Is there a way to select a precision?

[Simon Slavin]

On 6 May 2013, at 1:14pm, Paul van Helden  wrote:

> My point was about not storing binary junk - the part of a number that has
> no meaning because the accuracy of the inputs is limited. When you have a
> generic db manager that can show any table or if you are looking at the
> results of your own SQL statement, it helps to reduce clutter on the
> screen.

You should never be fetching a number from a database and showing it directly 
on the display.  The number needs to be formatted by your software first.  Does 
it need to be justified so a column of numbers lines up ?  How should negatives 
be shown ?  Do you want to show the decimal point as a comma or a stop ?  Do 
you use a thousands separator ?  Do you need a units symbol ?

Formatting numbers for pretty printing is not the job of a database system.  
The database stores the numbers and recalls them for you.  Figuring out how to 
show them onscreen is the programmer's job.

You can format them after retrieving them from the database, or you can format 
them before retrieving them from the database before putting them in the 
database, in which case you should consider that you're storing strings, not 
numbers.

> The data also compresses better.

Not the way things are done these days.  It's extremely rare to do semantic 
compression of individual values.  Almost all compression methods just take the 
entire file in one go.

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

Re: [sqlite] Is there a way to select a precision?

[Michael Black]

What you are talking about would be feature creep for SQLite.

Yes...other databases do respect NUMBER(10,2) on SELECT's.

SQLite is "light weight" and does no such magic for you.
So it does take an extra step.
You'll note that SQLite does provide a GUI for you to play with.
If it did it would probably allow you to format columns.

This keeps the library small and lightweight.

Can you simply use round to do what you want?
CREATE TABLE stuff(f number(10,2));
INSERT INTO "stuff" VALUES(1.0/3.0);
sqlite> select f from stuff;
0.333
sqlite> select round(f,2) from stuff;
0.33

Doesn't work, of course, for generic "table edit" in some GUI.
For that you could create a view.

sqlite> create view fview as select round(f,2) from stuff;
sqlite> select * from fview;

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden
Sent: Monday, May 06, 2013 7:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is there a way to select a precision?

> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that
> SQLite will attempt to store (string) values as integers first and floats
> second before giving up and storing strings.
>

This has nothing to do with my reply and I understand how it works.

>
> You do realize that there are decimal numbers that have infinite binary
> expansions?
>

I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
they are the same in SQLite).

>
> You are also talking presentation (as in formatting) of numeric values as
> opposed to representation (as in storing/retrieving). The former is best
> handled in the user interface while the latter is the subject of database
> engines.
>

My point was about not storing binary junk - the part of a number that has
no meaning because the accuracy of the inputs is limited. When you have a
generic db manager that can show any table or if you are looking at the
results of your own SQL statement, it helps to reduce clutter on the
screen. The data also compresses better.

>
> Fatihful reproduction of formatting would be possible using TEXT affinity
> and calling sqlite3_bind_text. Performing arithmetic with these "numbers"
> would however be tricky, slow and would still not guarantee that
calculated
> values would conform to the desired formatting.
>
> Of course, but in most cases we don't need to go this far. My main point
is about rounding before binding; my secondary point that scale in a column
definition can be desirable to avoid it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is there a way to select a precision?

[veneff]

You could always store the precision info in another column or two.

Vance

on May 06, 2013, Paul van Helden  wrote:
>
>> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that
>> SQLite will attempt to store (string) values as integers first and floats
>> second before giving up and storing strings.
>>
>
>This has nothing to do with my reply and I understand how it works.
>
>>
>> You do realize that there are decimal numbers that have infinite binary
>> expansions?
>>
>
>I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
>they are the same in SQLite).
>
>>
>> You are also talking presentation (as in formatting) of numeric values as
>> opposed to representation (as in storing/retrieving). The former is best
>> handled in the user interface while the latter is the subject of database
>> engines.
>>
>
>My point was about not storing binary junk - the part of a number that has
>no meaning because the accuracy of the inputs is limited. When you have a
>generic db manager that can show any table or if you are looking at the
>results of your own SQL statement, it helps to reduce clutter on the
>screen. The data also compresses better.
>
>>
>> Fatihful reproduction of formatting would be possible using TEXT affinity
>> and calling sqlite3_bind_text. Performing arithmetic with these "numbers"
>> would however be tricky, slow and would still not guarantee that calculated
>> values would conform to the desired formatting.
>>
>> Of course, but in most cases we don't need to go this far. My main point
>is about rounding before binding; my secondary point that scale in a column
>definition can be desirable to avoid it.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select a precision?

[Paul van Helden]

> A delared type of NUMBER(10,2) has NUMERIC affinity, which means that
> SQLite will attempt to store (string) values as integers first and floats
> second before giving up and storing strings.
>

This has nothing to do with my reply and I understand how it works.

>
> You do realize that there are decimal numbers that have infinite binary
> expansions?
>

I wouldn't store such numbers into a NUMBER(10,2), just a NUMBER (I know
they are the same in SQLite).

>
> You are also talking presentation (as in formatting) of numeric values as
> opposed to representation (as in storing/retrieving). The former is best
> handled in the user interface while the latter is the subject of database
> engines.
>

My point was about not storing binary junk - the part of a number that has
no meaning because the accuracy of the inputs is limited. When you have a
generic db manager that can show any table or if you are looking at the
results of your own SQL statement, it helps to reduce clutter on the
screen. The data also compresses better.

>
> Fatihful reproduction of formatting would be possible using TEXT affinity
> and calling sqlite3_bind_text. Performing arithmetic with these "numbers"
> would however be tricky, slow and would still not guarantee that calculated
> values would conform to the desired formatting.
>
> Of course, but in most cases we don't need to go this far. My main point
is about rounding before binding; my secondary point that scale in a column
definition can be desirable to avoid it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select a precision?

[Hick Gunter]

Maybe you should check out

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

A delared type of NUMBER(10,2) has NUMERIC affinity, which means that SQLite 
will attempt to store (string) values as integers first and floats second 
before giving up and storing strings.

You do realize that there are decimal numbers that have infinite binary 
expansions?

You are also talking presentation (as in formatting) of numeric values as 
opposed to representation (as in storing/retrieving). The former is best 
handled in the user interface while the latter is the subject of database 
engines.

Fatihful reproduction of formatting would be possible using TEXT affinity and 
calling sqlite3_bind_text. Performing arithmetic with these "numbers" would 
however be tricky, slow and would still not guarantee that calculated values 
would conform to the desired formatting.

-Ursprüngliche Nachricht-
Von: Paul van Helden [mailto:p...@planetgis.co.za]
Gesendet: Montag, 06. Mai 2013 10:05
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Is there a way to select a precision?

>
>
> What do you mean, select precision? The double value you pass to
> sqlite3_bind_double() will be used as is. Are you saying you want to
> round it first? Then go ahead and do that - I'm not sure what that has
> to do with SQLite.
> --
>
It is an issue with SQLite because the values in NUMBER(10,2) have no effect. 
Too often I see small values with 15 digits in a table because a double was 
passed as-is. It is not just about space, it is also about presentation. In 
engineering we are taught that the number of digits should also tell you the 
accuracy of the sample, so for example a latitude/longitude obtained from a 
handheld GPS should be stored with 6 decimal digits (~10cm), the rest is just 
junk. Since the database does not do this for you, when the programmer knows 
the accuracy of the sample, he shouldn't be lazy and instead do 
Round(Longitude*100)/100 before binding. Of course, when the data is 
presented it should be properly rounded with zeros added at the end or even 
zeros replacing digits to the left of the decimal (to indicate precision), but 
my point is you shouldn't store junk digits in the first place.

I love it that you don't have to specify TEXT and NUMBER lengths, but would 
have preferred that SQLite didn't ignore them when specified and that in a
NUMBER(p,s) column, the double is stored as an integer internally if p<=18.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select a precision?

[Paul van Helden]

>
>
> What do you mean, select precision? The double value you pass to
> sqlite3_bind_double() will be used as is. Are you saying you want to round
> it first? Then go ahead and do that - I'm not sure what that has to do with
> SQLite.
> --
>
It is an issue with SQLite because the values in NUMBER(10,2) have no
effect. Too often I see small values with 15 digits in a table because a
double was passed as-is. It is not just about space, it is also about
presentation. In engineering we are taught that the number of digits should
also tell you the accuracy of the sample, so for example a
latitude/longitude obtained from a handheld GPS should be stored with 6
decimal digits (~10cm), the rest is just junk. Since the database does not
do this for you, when the programmer knows the accuracy of the sample, he
shouldn't be lazy and instead do Round(Longitude*100)/100 before
binding. Of course, when the data is presented it should be properly
rounded with zeros added at the end or even zeros replacing digits to the
left of the decimal (to indicate precision), but my point is you shouldn't
store junk digits in the first place.

I love it that you don't have to specify TEXT and NUMBER lengths, but would
have preferred that SQLite didn't ignore them when specified and that in a
NUMBER(p,s) column, the double is stored as an integer internally if p<=18.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select a precision?

[Alessandro Marzocchi]

If what you want is a way to store floating point numbers with a precision
other than double (8 bytes) to decrease space usage or to get more
precisione the answer is no. The only native data types for sqlite are
integer (which can simulate bools), string, double and blob. Plus of course
NULL...
See http://www.sqlite.org/datatype3.html
Il giorno 06/mag/2013 05:05, "Igor Tandetnik"  ha
scritto:

> On 5/5/2013 8:24 PM, Igor Korot wrote:
>
>> Is there a way to select a precision either on the call to
>> sqlite3_bind_double() or before it?
>>
>
> What do you mean, select precision? The double value you pass to
> sqlite3_bind_double() will be used as is. Are you saying you want to round
> it first? Then go ahead and do that - I'm not sure what that has to do with
> SQLite.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to select a precision?

[Igor Tandetnik]

On 5/5/2013 8:24 PM, Igor Korot wrote:

Is there a way to select a precision either on the call to
sqlite3_bind_double() or before it?


What do you mean, select precision? The double value you pass to 
sqlite3_bind_double() will be used as is. Are you saying you want to 
round it first? Then go ahead and do that - I'm not sure what that has 
to do with SQLite.

--
Igor Tandetnik

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

[sqlite] Is there a way to select a precision?

[Igor Korot]

Hi, ALL,

Is there a way to select a precision either on the call to
sqlite3_bind_double() or before it?
Other than the call to sprintf()

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