Re: [sqlite] auto-incrementing integer in composite primary key (SQLite3 Solution)

[Kyle McKay]

On April 16, 2012 09:27:06 PDT, "Mr. Puneet Kishor" wrote:

Given

CREATE TABLE t (
id INTEGER NOT NULL,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP
PRIMARY KEY (id, created_on)
);

how can I make just the 'id' column auto-increment?


Here is an example of how to get the functionality you're looking for  
using SQLite3:


$ sqlite3 autoincrement-compound-key.sq3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .schema
CREATE TABLE example (
  id INTEGER UNIQUE NOT NULL,
  name TEXT,
  PRIMARY KEY (id, name)
);
CREATE TRIGGER example_autoincrement BEFORE INSERT ON example
  FOR EACH ROW WHEN NEW.id IS NULL BEGIN
INSERT INTO example VALUES (
  COALESCE((SELECT MAX(id) FROM example),0) + 1,
  NEW.name
);
SELECT RAISE(IGNORE);
  END;
sqlite> DELETE FROM example;
sqlite> INSERT INTO example (name) VALUES ('first'),('second'), 
('third');

sqlite> INSERT INTO example VALUES (10, 'ten');
sqlite> INSERT INTO example (name) VALUES ('eleven');
sqlite> SELECT * FROM example ORDER BY id;
1|first
2|second
3|third
10|ten
11|eleven
sqlite> .quit
$

The idea is to have the BEFORE INSERT trigger do the actual  
computation of the next auto increment value to use (but only when one  
was not already specified) AND the actual insertion of the NEW row and  
then use the special SQLite3 RAISE(IGNORE) functionality to skip the  
insertion of the NEW row that would normally have occurred immediately  
following the BEFORE INSERT trigger.


In this example, the id column is constrained to be UNIQUE which  
causes an index to be created that the SELECT MAX(id) expression in  
the trigger then uses for efficiency.  If you do not require the id  
column to be UNIQUE, and instead only require that if id is not set,  
the max(id) + 1 value is used, you can remove the UNIQUE constraint  
and then add a separate CREATE INDEX for the id column so that the  
SELECT MAX(id) expression remains efficient in the context of a large  
number of rows.


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

Re: [sqlite] auto-incrementing integer in composite primary key

[Kees Nuyt]

On Mon, 16 Apr 2012 11:27:06 -0500, "Mr. Puneet Kishor"
 wrote:

>Given
>
>   CREATE TABLE t (
>   id INTEGER NOT NULL,
>   created_on DATETIME DEFAULT CURRENT_TIMESTAMP
>   PRIMARY KEY (id, created_on)
>   );
>
>how can I make just the 'id' column auto-increment?

I found this on the interwebs a long time ago, I didn't bookmark it and
forgot who the author was. 

The original version was a bash script which used a separate notes table
and an audit table, I rewrote it to use a writable view (instead of
trigger) plus a demo case, leaving out interactive use.

The advantage of a separate notes table (and after insert/update/delete
triggers) is the autoincrement you are looking for, the disadvantage is
having two tables. Let me know if you want me to post that version as
well.

Anyway, I hope it inspires you, or any other reader.

The demo code runs slow, because it has to sleep a lot to demonstrate
evolving lifetime.

It's slightly tested, obviously the julianday('some date in the very
remote future') should be optimmized into proper real literals]

<===>
#!/usr/bin/sh
#
# takes notes, keep an audit trail
#
DB=/tmp/notes.sqlite
createdb () {
sqlite3 "${DB}" <<'EOSQL'
PRAGMA page_size=4096;
PRAGMA cache_size=100;
PRAGMA default_cache_size=100;

BEGIN TRANSACTION;

 DROP TABLE IF EXISTS audit;
 CREATE TABLE audit (
  nkey  INTEGER
, timeFrREAL
, timeToREAL DEFAULT (julianday('2199-12-31 23:59:59'))
, category  TEXT
, msg   TEXT
, PRIMARY KEY (nkey,timeFr)
);

 DROP VIEW IF EXISTS notes;
 CREATE VIEW notes AS
 SELECT
nkey, category, msg
FROM audit
WHERE timeTo > julianday('2199-12-31 00:00:00');

 CREATE TRIGGER audit_insert_notes INSTEAD OF INSERT ON notes
 FOR EACH ROW BEGIN
INSERT INTO audit (nkey, category, msg, timeFr)
VALUES (new.nkey, new.category, new.msg, julianday('now'));
 END;

 CREATE TRIGGER audit_update_notes INSTEAD OF UPDATE ON notes
 FOR EACH ROW BEGIN 
UPDATE audit SET timeTo = julianday('now')
WHERE nkey == new.nkey AND timeTo > julianday('2199-12-31 00:00:00');
INSERT INTO audit (nkey, category, msg, timeFr)
VALUES (new.nkey, new.category, new.msg, julianday('now'));
  END;

 CREATE TRIGGER audit_delete_notes INSTEAD OF DELETE ON notes
 FOR EACH ROW BEGIN
UPDATE audit SET timeTo   = julianday('now')
WHERE nkey == old.nkey AND timeTo > julianday('2199-12-31 00:00:00');
  END;

 DROP VIEW IF EXISTS sh_notes;
 CREATE VIEW sh_notes AS
 SELECT nkey
, datetime(timeFr)
, datetime(timeTo)
, category, msg
, CAST ((julianday('now') - timeFr) * 86400 AS INTEGER)
AS lifetimeseconds
 FROM audit WHERE timeTo > julianday('2199-12-31 00:00:00') 
 ORDER BY nkey,timeFr;

 DROP VIEW IF EXISTS sh_audit;
 CREATE VIEW sh_audit AS
 SELECT nkey
, datetime(timeFr)
, datetime(timeTo)
, category, msg
, CAST ((CASE
WHEN timeTo > julianday('2199-12-31 00:00:00') THEN julianday('now')
ELSE timeTo
END - timeFr) * 86400 AS INTEGER)
   AS lifetimeseconds
 FROM audit
 ORDER BY nkey,timeFr;

 DROP VIEW IF EXISTS sh_status;
 CREATE VIEW sh_status AS
 SELECT nkey
, COUNT( CASE
WHEN timeTo > julianday('2199-12-31 00:00:00') THEN 1 
ELSE NULL END) AS NrActive
, COUNT( CASE 
WHEN timeTo < julianday('now') THEN 1
ELSE NULL END) AS NrDeleted
 FROM audit
 GROUP BY nkey;

 COMMIT;
EOSQL
}

insrow () { # nkey, cat#, version#
 printf "INSERT INTO notes (nkey,category,msg) VALUES (%d,'cat%d','note
%d v%d');\n" $1 $2 $1 $3
 sleep 3
}

updrow () { # nkey, cat#, version#
 printf "UPDATE notes set category='cat%d',msg='note %d v%d' WHERE
nkey=%d;\n" $2 $1 $3 $1
 sleep 2
}

delrow () { # nkey
 printf "DELETE FROM notes WHERE nkey = %d;\n" $1
 sleep 1
}

reportall () {
 sqlite3 "${DB}" <<'EOSQL'
.header on
.mode column
.echo on

 SELECT nkey,category,msg FROM notes;
 SELECT * FROM sh_notes;
 SELECT nkey,datetime(timeFr),datetime(timeTo),category,msg FROM audit;
 SELECT * FROM sh_audit;
 SELECT * FROM sh_status;
EOSQL
}

report () {
 sqlite3 "${DB}" <<'EOSQL'
.header on
.mode column
.echo on
 SELECT * FROM sh_notes;
 SELECT * FROM sh_audit;
EOSQL
}

demo () {
{
printf ".echo on\n"
# params: nkey, cat#, version#
insrow 1 3 1
insrow 2 5 1 
updrow 1 3 2
updrow 2 4 2
insrow 3 2 1
updrow 3 2 2
delrow 3
} | sqlite3 "${DB}"
}

### MAIN ###
test -f "${DB}" && rm "${DB}"
createdb
demo
reportall
sleep 5
report
<===>


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 2:45 PM, Richard Hipp  wrote:
> On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote:
>> Note that you'll lose any fractional second information when you do
>> this.  On the other hand, fractional second information does not sort
>> properly when compared as text,
>
> Huh?  The standard format is -MM-DD HH:MM:SS.SSS.  I think that sorts
> in time order when compared as strings.  Do you have a counter-example?

I could swear that I observed %f produce variable numbers of digits
for the decimal part.  But now I don't see that.  An Emily Litella
moment for me.

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

Re: [sqlite] Time zones

[Igor Tandetnik]

jwzumwalt  wrote:
> Where can I find a complete list of Sqlite timezones. I Google searched
> without success.
> 
> i.e
> hawaiin ??
> date_default_timezone_set('America/Los_Angeles'); // pacific timezone

date_default_timezone_set is not part of SQLite. You must be using some other 
library - possibly PHP:

http://php.net/manual/en/function.date-default-timezone-set.php
http://www.php.net/manual/en/timezones.php

The examples you show appear to be coming from Olson database:

http://www.iana.org/time-zones
http://en.wikipedia.org/wiki/Tz_database
http://en.wikipedia.org/wiki/List_of_tz_database_time_zones

If so, Hawaiian time should be represented as Pacific/Honolulu
-- 
Igor Tandetnik

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

Re: [sqlite] Time zones

[Black, Michael (IS)]

You're talking PHP...not SQLite.



SQLite doesn't know about timezones other than "local" and "utc".



So your timezones will depend on your OS.



On RedHat it's in /usr/share/zoneinfo and there's tons of them.  I've got 1,743 
of them.







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of jwzumwalt [jwzumw...@neatinfo.com]
Sent: Tuesday, April 17, 2012 3:59 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Time zones


Where can I find a complete list of Sqlite timezones. I Google searched
without success.

i.e
hawaiin ??
date_default_timezone_set('America/Los_Angeles'); // pacific timezone
date_default_timezone_set('America/Denver'); // mountain timezone
central ??
date_default_timezone_set('America/New_York); // eastern timezone

thanks
--
Jan Zumwalt
--
View this message in context: 
http://old.nabble.com/Time-zones-tp33704187p33704187.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Time zones

[jwzumwalt]

Where can I find a complete list of Sqlite timezones. I Google searched
without success.

i.e
hawaiin ??
date_default_timezone_set('America/Los_Angeles'); // pacific timezone
date_default_timezone_set('America/Denver'); // mountain timezone
central ??
date_default_timezone_set('America/New_York); // eastern timezone

thanks
-- 
Jan Zumwalt
-- 
View this message in context: 
http://old.nabble.com/Time-zones-tp33704187p33704187.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Richard Hipp]

On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote:

> On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones 
> wrote:
> > I think I'll spend the time going back and storing the dates as integer
> > time (since the epoch) as Nico suggested and just use strftime to convert
> > them as and when required.
>
> Note that you'll lose any fractional second information when you do
> this.  On the other hand, fractional second information does not sort
> properly when compared as text,


Huh?  The standard format is -MM-DD HH:MM:SS.SSS.  I think that sorts
in time order when compared as strings.  Do you have a counter-example?



> so if you need sub-second resolution
> you need to work a little harder.
>
> (Huh, that is strange.  Is there a canonical way to compare timestamps
> with fractional seconds in SQLite3?)
>

The julian day number, stored as a double-precision floating point number
gives you millisecond resolution in the modern era.  Julian day number is
the default and preferred format for dates/times in SQLite.


sqlite> select datetime('now'), julianday('now');
2012-04-17 19:45:11|2456035.32305485





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



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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Black, Michael (IS)]

Store them as float or do integer and multiple by a power of 10 to get as many 
digits as you want.



So 1.234 seconds *10^3 can be 1234 integer







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Tuesday, April 17, 2012 1:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Slightly unexpected behaviour when comparing date 
and datetime

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones  wrote:
> I think I'll spend the time going back and storing the dates as integer
> time (since the epoch) as Nico suggested and just use strftime to convert
> them as and when required.

Note that you'll lose any fractional second information when you do
this.  On the other hand, fractional second information does not sort
properly when compared as text, so if you need sub-second resolution
you need to work a little harder.

(Huh, that is strange.  Is there a canonical way to compare timestamps
with fractional seconds in SQLite3?)
___
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] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones  wrote:
> I think I'll spend the time going back and storing the dates as integer
> time (since the epoch) as Nico suggested and just use strftime to convert
> them as and when required.

Note that you'll lose any fractional second information when you do
this.  On the other hand, fractional second information does not sort
properly when compared as text, so if you need sub-second resolution
you need to work a little harder.

(Huh, that is strange.  Is there a canonical way to compare timestamps
with fractional seconds in SQLite3?)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Mark Jones]

Thanks goes out to both of you for your quick responses!

For text in SQLite, delimit with single quotes, not double quotes.  Double
> quotes are used for tricky entity names.  And you probably don't want the
> quotes around the real numbers at all.


I'll go off and re-read up on the quotes and make sure I use them
correctly.

And you're correct, I didn't want the quotes around the real numbers, they
were put in there when I was putting together and testing my sample
database. But not in the real database. *facepalm*

I think I'll spend the time going back and storing the dates as integer
time (since the epoch) as Nico suggested and just use strftime to convert
them as and when required.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Nico Williams]

On Tue, Apr 17, 2012 at 10:45 AM, Mark Jones  wrote:
> Afternoon all,
>
> I have the following schema:
>
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);
>
> And the following sample data:
>
> INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
> INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
> INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
> INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");
>
> When I perform the following query I don't get the expected result:
>
> SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
> 2|2012-01-02|6.0
> 3|2012-01-03|7.0
> 4|2012-01-04|5.0
>
> Why does this happen? Is this the sort of thing I should bother the SQLite
> develops with (via bug report)?

Because you're doing *string* comparison of strings that represent
date, on the one hand, and date + time on the other.  SQLite3 does not
have a native date/time type, and so has no native date/time
comparison operator either.  You can still do the comparison you want,
but you have to do it slightly differently.  Here's one option:

SELECT * FROM day WHERE datetime(timestamp) >= '2012-01-01 00:00:00';

Another option would be to convert to seconds since the Unix epoch and
then use integer comparison:

SELECT * FROM day WHERE strftime('%s', timestamp) >=
strftime('%s', '2012-01-01 00:00:00');

See http://sqlite.org/lang_datefunc.html

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

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

[Simon Slavin]

On 17 Apr 2012, at 4:45pm, Mark Jones  wrote:

> I have the following schema:
> 
> CREATE TABLE day
> ("id" INTEGER PRIMARY KEY,
> "timestamp" DATETIME,
> "value" REAL);

There is no such datatype as 'DATETIME'.  You are actually storing text.  See 
especially section 1.2, but possibly the whole page of



> And the following sample data:
> 
> INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
> INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
> INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
> INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");

For text in SQLite, delimit with single quotes, not double quotes.  Double 
quotes are used for tricky entity names.  And you probably don't want the 
quotes around the real numbers at all.

> When I perform the following query I don't get the expected result:
> 
> SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
> 2|2012-01-02|6.0
> 3|2012-01-03|7.0
> 4|2012-01-04|5.0

The string '2012-01-01' sorts before the string '2012-01-01 00:00:00'.  Your 
problem is that your strings are being understood and stored as strings, not a 
way of using a string to specify a timestamp.  You could specify times in your 
INSERT commands ...

INSERT INTO day VALUES (NULL, '2012-01-01 00:00:00', 5.0);

or you might choose to store just dates as you already do, and do your SELECT 
using just the date:

SELECT * FROM day WHERE timestamp >= '2012-01-01';

but since you call the field "timestamp" you probably want to figure times in 
it.

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

[sqlite] Slightly unexpected behaviour when comparing date and datetime

[Mark Jones]

Afternoon all,

I have the following schema:

CREATE TABLE day
("id" INTEGER PRIMARY KEY,
"timestamp" DATETIME,
"value" REAL);

And the following sample data:

INSERT INTO day VALUES (NULL, "2012-01-01", "5.0");
INSERT INTO day VALUES (NULL, "2012-01-02", "6.0");
INSERT INTO day VALUES (NULL, "2012-01-03", "7.0");
INSERT INTO day VALUES (NULL, "2012-01-04", "5.0");

When I perform the following query I don't get the expected result:

SELECT * FROM day WHERE timestamp >= '2012-01-01 00:00:00';
2|2012-01-02|6.0
3|2012-01-03|7.0
4|2012-01-04|5.0

Why does this happen? Is this the sort of thing I should bother the SQLite
develops with (via bug report)?

*(I know I can do either of the following to get the expected result:*
*
*
*SELECT * FROM day WHERE timestamp >= date('2012-01-01 00:00:00');*
*SELECT * FROM day WHERE datetime(timestamp) >= '2012-01-01 00:00:00';)*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How point numbers are they stored in sqlite?

[Paxdo Presse]

ok, thank you all!

Le 17 avr. 2012 à 11:35, Richard Hipp a écrit :
> 
> Key point:  Floating point numbers are approximations.  This is an inherent
> property of IEEE floating point numbers, not a limitation of SQLite.  If
> you need an exact answer, use integers.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how much "case...when..." command?

[Igor Tandetnik]

YAN HONG YE  wrote:
> UPDATE bb SET Slevel =
>CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
>CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
>CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
>CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
> case...
> csse...
> ...
>CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;

You can simplify this statement to

update bb set Slevel=
  (price1>12) + (price1>30) + (price2>20) + (price2>30) + ... ;

Also, if thresholds are regular (as they seem to be), you should be able to 
calculate the level with a formula, rather than a bunch of cases. For example, 
if you want to add a level for every 10-point increment in price2, you could do 
something like

min(max((price2 - 11) / 10, 0), 7)

-- 
Igor Tandetnik

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

Re: [sqlite] How point numbers are they stored in sqlite?

[Jean-Denis MUYS]

On 17 avr. 2012, at 11:35, Richard Hipp wrote:

On Tue, Apr 17, 2012 at 5:12 AM, Paxdo Presse 
> wrote:


Hi,

How point numbers are they stored in sqlite?

In a field with REAL affinity:

round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-),
but "5,78939997" in the cursor of my development language
when I get it to sqlite.

Internally, SQLite works with 5.7894 or 5,78939997?


Neither.  SQLite uses double-precision IEEE floating point numbers.  And
the value 5.7894 is not representable as an IEEE float.  The closest you
can get is 5.789399965751840136363171041011810302734375.  SQLite
only attempts to preserve the first 15 significant digits of a floating
point value, so SQLite is likely to print that number as 5.7894.  But if
your development language tries to show 16 digits of precision (as
apparently it does) then you might see the second value.

Key point:  Floating point numbers are approximations.  This is an inherent
property of IEEE floating point numbers, not a limitation of SQLite.  If
you need an exact answer, use integers.


Perhaps it's worth mentioning again David Goldberg's seminal paper "What Every 
Computer Scientist Should Know About Floating-Point Arithmetic". That paper is 
so well known it's been reprinted many times and is available in many places on 
the internet. For example Oracle put a reprint in its documentation:

http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

You could do worse than reading it. Twice.

Jean-Denis

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

Re: [sqlite] How point numbers are they stored in sqlite?

[ajm]

> From: Paxdo Presse 
> To:  General Discussion of SQLite Database 
> Date:  Tue, 17 Apr 2012 11:12:45 +0200
> Subject:  [sqlite] How point numbers are they stored in sqlite?
>
>Hi,
>
>How point numbers are they stored in sqlite?
>
>In a field with REAL affinity:
>
>round(11578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-), 
>but "5,78939997" in the cursor of my development language
>when I get it to sqlite.
>
>Internally, SQLite works with 5.7894 or 5,78939997?
>
>This is important because it can cause problems rounding.
>ditto for the calculations on decimal currency.
>

I believe that for accounting purposes, basides SQLite, the best solution is to 
use an adequate library such as the IBM decNumber package.

HTH.

Adolfo J. Millan
Zator Systems.


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

Re: [sqlite] How point numbers are they stored in sqlite?

[Richard Hipp]

On Tue, Apr 17, 2012 at 5:12 AM, Paxdo Presse  wrote:

>
> Hi,
>
> How point numbers are they stored in sqlite?
>
> In a field with REAL affinity:
>
> round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-),
> but "5,78939997" in the cursor of my development language
> when I get it to sqlite.
>
> Internally, SQLite works with 5.7894 or 5,78939997?
>

Neither.  SQLite uses double-precision IEEE floating point numbers.  And
the value 5.7894 is not representable as an IEEE float.  The closest you
can get is 5.789399965751840136363171041011810302734375.  SQLite
only attempts to preserve the first 15 significant digits of a floating
point value, so SQLite is likely to print that number as 5.7894.  But if
your development language tries to show 16 digits of precision (as
apparently it does) then you might see the second value.

Key point:  Floating point numbers are approximations.  This is an inherent
property of IEEE floating point numbers, not a limitation of SQLite.  If
you need an exact answer, use integers.


>
> This is important because it can cause problems rounding.
> ditto for the calculations on decimal currency.
>
> Greetings
> olivier
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] How point numbers are they stored in sqlite?

[Paxdo Presse]

Hi,

How point numbers are they stored in sqlite?

In a field with REAL affinity:

round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-), 
but "5,78939997" in the cursor of my development language
when I get it to sqlite.

Internally, SQLite works with 5.7894 or 5,78939997?

This is important because it can cause problems rounding.
ditto for the calculations on decimal currency.

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

Re: [sqlite] how much "case...when..." command?

[Kit]

2012/4/17 YAN HONG YE :
> UPDATE bb SET Slevel =
>    CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
>    CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
>    CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
>    CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
> case...
> csse...
> ...
>    CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;
> in this command, I don't know sqlite allow how much "case...when..." in one 
> sql command.

The maximum number of bytes in the text of an SQL statement is limited
to SQLITE_MAX_SQL_LENGTH which defaults to 100. You can redefine
this limit to be as large as the smaller of SQLITE_MAX_LENGTH and
1073741824.

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

[sqlite] how much "case...when..." command?

[YAN HONG YE]

UPDATE bb SET Slevel =
CASE price1>12 WHEN 1 THEN 1 ELSE 0 END +
CASE price1>30 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>20 WHEN 1 THEN 1 ELSE 0 END +
CASE price2>30 WHEN 1 THEN 1 ELSE 0 END +
case...
csse...
...
CASE price2>80 WHEN 1 THEN 1 ELSE 0 END;
in this command, I don't know sqlite allow how much "case...when..." in one sql 
command.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users