SQLite Forum

sqlite3 rounding off the values of timestamp causing issues
Login

sqlite3 rounding off the values of timestamp causing issues

(1) By Shreyas (shreyask) on 2020-11-23 07:37:00 [source]

Hi, I am facing issues with the database values after converting a sqlite2 database to sqlite3.

To provide some context, I have a table XYZ and it has a few rows that stores timestamps.

The structure of the table is, CREATE TABLE "xyz" ( "xyz_id" INTEGER NOT NULL, "xyz_value" VARCHAR NOT NULL, "xyz_order" INTEGER DEFAULT 0 );

The column xyz_value is a VARCHAR and I read and write it as a string. Although, while converting the db from sqlite2-3, I went ahead with the below approach,

# sqlite mydata.db .dump | sqlite3 new_mydataV3.db

This did convert the db to sqlite3 but part of the code in my application that handles timestamp fails as the timestamp in the xyz_value was rounded off during this conversion.

I tried dumping the sqlite2 db to a file and examining if the values were rounded off to begin with. The dump file appears to be fine and has the values intact.

To reproduce the issue,

Create a table in sqlite2,

# cat dump.sql

CREATE TABLE "value_tbl" ( "value_id" INTEGER NOT NULL,"value_value" VARCHAR NOT NULL,"value_order" INTEGER DEFAULT 0); INSERT INTO value_tbl VALUES(1,1536273869.654473892,0); COMMIT;

# sqlite new.db < dump.sql

# sqlite new.db .dump

CREATE TABLE "xyz" ( "xyz_id" INTEGER NOT NULL,"xyz_value" VARCHAR NOT NULL,"xyz_order" INTEGER DEFAULT 0); INSERT INTO xyz VALUES(1,1536273869.654473892,0); COMMIT;

The values seem intact over here.

Now converting it,

# sqlite new.db .dump | sqlite3 newV3.db

# sqlite3 newV3.db .dump

CREATE TABLE IF NOT EXISTS "xyz" ( "xyz_id" INTEGER NOT NULL,"xyz_value" VARCHAR NOT NULL,"xyz_order" INTEGER DEFAULT 0); INSERT INTO xyz VALUES(1,'1536273869.65447',0); COMMIT;

The value was rounded off to 15 decimals.

The floating point does have an accuracy upto 15 digits but we know from the metadata of table, the values are strings. Why are we rounding the values to 15 digits?

(2) By Keith Medcalf (kmedcalf) on 2020-11-23 09:12:42 in reply to 1 [link] [source]

The dump from sqlite2 is not a text value. It is a real value. Note there are no quotation marks around the value.

# sqlite new.db .dump

CREATE TABLE "xyz" ( "xyz_id" INTEGER NOT NULL,"xyz_value" VARCHAR NOT NULL,"xyz_order" INTEGER DEFAULT 0); INSERT INTO xyz VALUES(1,1536273869.654473892,0); COMMIT;

So when this statement is executed by SQLite3 the value treated as a REAL (Double Precision Floating Point -- not as a text string) and converted to text (by printing it, basically) for storage in the text field.

You may not like this but I assume that this is just how sqlite2 works when it exports text that looks like a number.

(3) By Gunter Hick (gunter_hick) on 2020-11-23 12:06:14 in reply to 1 [link] [source]

You should make up your mind regarding the type of a "timestamp".

If, as your example suggests, you are using a floating point representation the you should declare the column as REAL.

If you decide to switch to ISO time strings, then you should declare the column as CHAR or TEXT (there is no VARCHAR in SQLite).

With a declared storage class of TEXT, you are forcing SQLite to convert REAL to TEXT somewhere along the line, which is documented to preserve only 15 significant digits.

asql> select cast(1536273869.654473892 as text); 1536273869.65447

(4.2) By Keith Medcalf (kmedcalf) on 2020-11-23 12:55:31 edited from 4.1 in reply to 1 [link] [source]

The value, when stored as a floating point value, and converted to hooman readable text, to 26 decimal places, is:

┌────────────────────────────────────────┐
│ printf('%!.26f', 1536273869.654473892) │
├────────────────────────────────────────┤
│ 1536273869.6544737815856933            │
└────────────────────────────────────────┘
sqlite>

The default conversion from REAL (IEEE-754 double precision floating point) to TEXT does not display digits of superfluous precision because it is generally assumed that if you do such conversions that you are not interested in accuracy but rather prettiness, otherwise you would have left the floating point value as a floating point value, which IS accurate.

Converting the REAL to TEXT with 9 digits after the decimal point yields:

┌───────────────────────────────────────┐
│ printf('%!.9f', 1536273869.654473892) │
├───────────────────────────────────────┤
│ 1536273869.654473782                  │
└───────────────────────────────────────┘
sqlite>

The difference is either because someone's compiler or hardware is not IEEE-754 compliant or the source used more precision than could be contained in an IEEE-754 double.

(5) By Richard Hipp (drh) on 2020-11-23 12:57:19 in reply to 1 [link] [source]

You can force the ".dump" command of sqlite version 2 to output only string literals and not numeric literals using the following patch to the "shell.c" file:

Index: src/shell.c
==================================================================
--- src/shell.c
+++ src/shell.c
@@ -385,22 +385,24 @@
       fprintf(p->out,"</TR>\n");
       break;
     }
     case MODE_Insert: {
       if( azArg==0 ) break;
       fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);
       for(i=0; i<nArg; i++){
         char *zSep = i>0 ? ",": "";
         if( azArg[i]==0 ){
           fprintf(p->out,"%sNULL",zSep);
+#if 0
         }else if( sqliteIsNumber(azArg[i]) ){
           fprintf(p->out,"%s%s",zSep, azArg[i]);
+#endif
         }else{
           if( zSep[0] ) fprintf(p->out,"%s",zSep);
           output_quoted_string(p->out, azArg[i]);
         }
       }
       fprintf(p->out,");\n");
       break;
     }
   }
   return 0;

(6) By Shreyas (shreyask) on 2020-11-24 07:23:19 in reply to 2 [link] [source]

Thanks Keith! I will proceed to handle the timestamps better by converting them as texts.

(7) By Shreyas (shreyask) on 2020-11-24 07:25:59 in reply to 3 [link] [source]

Thanks for the reply Gunter! I think sqlite shouldn't have an issue with VARCHAR but I tried adding the datatype as TEXT to see if that made any difference. I dont see any change in the way sqlite treats TEXT or VARCHAR. The issue is with the way sqlite2 dumps the values. I see values that are alphabetical having quotes around them but the numeric strings (such as the timestamps) are printed without quotes. This confuses the sqlite3 when it reads the queries.

# sqlite new.db .dump

BEGIN TRANSACTION; CREATE TABLE "value_tbl" ("value_id" INTEGER NOT NULL,"value_value" TEXT NOT NULL,"value_order" INTEGER DEFAULT 0); INSERT INTO value_tbl VALUES(1,1536738388.3623876328,0); COMMIT;

# sqlite new.db .dump | sqlite3 letsee.db

# sqlite3 letsee.db .dump

PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "value_tbl" ("value_id" INTEGER NOT NULL,"value_value" TEXT NOT NULL,"value_order" INTEGER DEFAULT 0); INSERT INTO value_tbl VALUES(1,'/lib/svc/manifest/application/security/tcsd.xml',0); INSERT INTO value_tbl VALUES(2,'1536738388.36239',0); INSERT INTO value_tbl VALUES(2,'1536738388.36239',0); COMMIT;

(8) By Shreyas (shreyask) on 2020-11-24 07:31:43 in reply to 5 [link] [source]

This would mean it would start dumping every column as a string and I need to preserve the integrity of the other columns and their datatypes. Am I understanding it right?

(9) By Gunter Hick (gunter_hick) on 2020-11-24 11:20:20 in reply to 7 [link] [source]

The problem is you are storing REAL values in a TEXT column.

asql> create table tvr (t text, r real); asql> insert into tvr values (1536738388.3623876328,1536738388.3623876328); asql> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE tvr (t text, r real); INSERT INTO "tvr" VALUES('1536738388.36239',1.53673838836238765715e+09); COMMIT;

(10.1) By Richard Hipp (drh) on 2020-11-24 11:39:23 edited from 10.0 in reply to 8 [link] [source]

In SQLite2, everything is always a string - there are no other datatypes.

When you export into SQLite3, if your column is of type INTEGER or REAL and the input data is a string (enclosed in '...') but looks like an integer or floating-point number, then it will be automatically converted into the integer or float. Thus in:

   CREATE TABLE demo1(a INT, b REAL, c TEXT);
   INSERT INTO demo1(a,b,c) VALUES('123','456.78e-1','987');

Even though all the inputs are string literals, the "a" column ends up holding an integer 123, the "b" column ends up with a numeric 45.678† value. The "c" column has type TEXT so it continues to be held as a string even though it looks like an integer.


† Floating point values are stored as IEEE-754 doubles, which means the value actually stored is an IEEE-754 double approximation to 45.678, which is 45.6779999999999972715158946812152862548828125

(11) By Shreyas (shreyask) on 2020-11-24 12:21:34 in reply to 9 [link] [source]

Thanks for the response, Gunter!

I understand the confusion. The issue is not with adding the values as real values instead of strings. I can clear that by showing you this.

Using sqlite2, created a new database, sqlite> create table xyz(id INTEGER, value TEXT); sqlite> insert into xyz values(1, "1234"); sqlite> insert into xyz values(2, "12323453243.234234324");

# sqlite repo_v2 .dump

BEGIN TRANSACTION; create table xyz(id INTEGER, value TEXT); INSERT INTO xyz VALUES(1,1234); INSERT INTO xyz VALUES(2,12323453243.234234324); COMMIT;

You see the quotes are gone for the numbers even though I inserted it as a string.

And sqlite3 might not be at fault as it perceives it as a real number due to the absence of quotes around the values but sqlite2 shouldn't be dumping it without the quotes.