Re: [sqlite] Storing large numeric values

[Jay A. Kreibich]

On Sat, Jan 02, 2010 at 10:01:42AM +, Simon Slavin scratched on the wall:
> 
> On 2 Jan 2010, at 9:54am, Bert Nelsen wrote:
> 
> > I am trying to save values like 19.000.000.000 to my database but I haven't
> > found the appropriate column type yet. Can anybody help please.
> > I am using the dhRichClient command object, but even Int64 isn't large
> > enough.

> If you need values that large you can't use INTEGER.

  Yes you can.  That number isn't all that big.
  
  As the link you posted says, integers in SQLite auto scale to 64 bits. 
  That's -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.  
  
  The number 19,000,000,000 only requires 36 bits of signed space,
  meaning SQLite will store it as an integer in only 5 bytes.

> If you need precision to the 1 then you can't use REAL. 

  Again, yes you can.  SQLite uses 64 bit floats.  These offer up to 52
  bits (53 normalized) bits of representation while keeping 1 bit
  precision (i.e. integer precision).  Since the sign is stored
  elsewhere with floats, we only need 35 of those bits.


  More to the point:

sqlite> CREATE TABLE t ( i INTEGER, r REAL );
sqlite> INSERT INTO t VALUES ( 190, 190 );
sqlite> INSERT INTO t VALUES ( 191, 191 );
sqlite> INSERT INTO t VALUES ( 190.0, 190.0 );
sqlite> INSERT INTO t VALUES ( 191.0, 191.0 );
sqlite> SELECT * FROM t;
190|190.0
191|191.0
190|190.0
191|191.0

  No problem.


  It should also be noted that SQLite does not follow
  internationalizations and always uses the character "." as the
  radix point.  See: http://www.sqlite.org/lang_expr.html (Literal Values)

  If you're having other problems, I'd suspect the wrapper you're using
  is messing with the values before they get to the database.

-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing large numeric values

[Artur Reilin]

Sorting wasn't planned, but it's a nice idea. At least i planned to use  
Top 5 or Top 10 of the highest values for my little project. So (A) would  
be the best idea. Math doesn't required at my current point. That's a  
great format and also easy to read.

Thanks Simon.

--

Am 02.01.2010, 11:55 Uhr, schrieb Simon Slavin :

>
> On 2 Jan 2010, at 10:11am, Artur Reilin wrote:
>
>> According to this I have also a question. I use some values for some
>> gamedata and use the played days, hours, minutes and seconds and put  
>> them
>> in one column. I use . as an seperator. Does it still is an integer or  
>> do
>> i need to change the column type to text or blob?
>
> Any text with two '.'s in is not an integer.  Integers do not have  
> decimal points in.  So it was probably storing those as TEXT.  If you do  
> want to store such a value in sortable form you have two simple options:
>
> A) Use a fixed-lenght format like DDDHHMMSS and store it as TEXT.
> B) Calculate all values as seconds and store the number as seconds as an  
> INTEGER.
>
> (A) makes it easy to format for printing and (B) makes it easy to do  
> maths.  If, however, you don't care about sorting, indexing or maths  
> there's no reason why you shouldn't continue to do it as you already do  
> it.
>
> Simon.
> ___
> 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] Storing large numeric values

[Artur Reilin]

According to this I have also a question. I use some values for some  
gamedata and use the played days, hours, minutes and seconds and put them  
in one column. I use . as an seperator. Does it still is an integer or do  
i need to change the column type to text or blob?

Using 4 columns - one for each thing would be not really a good thing,  
because it easier for me to separate them in one array as have each alone.  
I thought a day value would also not the real thing. Or does it would be  
better?

- Artur -

---

Am 02.01.2010, 11:01 Uhr, schrieb Simon Slavin :

>
> On 2 Jan 2010, at 9:54am, Bert Nelsen wrote:
>
>> I am trying to save values like 19.000.000.000 to my database but I  
>> haven't
>> found the appropriate column type yet. Can anybody help please.
>> I am using the dhRichClient command object, but even Int64 isn't large
>> enough.
>
> There are only five datatypes in SQLite3, and one of them doesn't store  
> a value:
>
> 
>
> If you need values that large you can't use INTEGER.  If you need  
> precision to the 1 then you can't use REAL.  So you can store those only  
> as TEXT or BLOB.  Would TEXT work for you ?
>
> Simon.
> ___
> 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] Storing large numeric values

[Simon Slavin]

On 2 Jan 2010, at 9:54am, Bert Nelsen wrote:

> I am trying to save values like 19.000.000.000 to my database but I haven't
> found the appropriate column type yet. Can anybody help please.
> I am using the dhRichClient command object, but even Int64 isn't large
> enough.

There are only five datatypes in SQLite3, and one of them doesn't store a value:



If you need values that large you can't use INTEGER.  If you need precision to 
the 1 then you can't use REAL.  So you can store those only as TEXT or BLOB.  
Would TEXT work for you ?

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

[sqlite] Storing large numeric values

[Bert Nelsen]

I am trying to save values like 19.000.000.000 to my database but I haven't
found the appropriate column type yet. Can anybody help please.
I am using the dhRichClient command object, but even Int64 isn't large
enough.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users