I get wrong values from the database when using node-sqlite

[sam1919]

I have a table column where I store large numbers (seconds since 1970).

The bigints are stored correctly (I checked it on the sqlite console) but with the sqlite driver I get negative(!) values. What's wrong here?

sqlite3 console:
sqlite> SELECT date FROM seen WHERE user='root' COLLATE NOCASE;
1296333940003

node-sqlite:
{ date: -746187876 }

I also tried "BIGINT UNSIGNED" with the same (wrong) result.

[Deleted user]

Hi Sam,

It seems your 'seconds' values are actually in milliseconds.
If you SELECT date/1000 you should get correct results until 2038 or so ;)

I have committed a "fix" for this issue in changeset 20ed195db941163848313f74755d77ec0c0f6084. The cause was the node-sqlite code calling sqlite3_column_int rather than sqlite3_column_int64.

Sadly there is no support for 64bit integers in Node JS (that I know of) so the numbers are represented as doubles ... so you only get 52 bits of precision.

We could consider using a BigNum library for these values...

[orlandov]

Sam, what you're running into is an unfortunate cross-section of limitations present both in JavaSript and SQLite. SQLite's type affinity is forcing the driver to interpret the value in your select as an INTEGER column type. As Steven mentioned, JavaScript doesn't support very large integers. Because SQLite stores everything under the hood as strings, you could simply request your data as TEXT using CAST:

sqlite = require('./sqlite');
common = require('./tests/lib/common.js');

db = new sqlite.Database();

db.open(":memory:", function (error) {
  if (error) {
    console.log("Tonight. You.");
    throw error;
  }

  common.createTable
    ( db
    , 'a'
    , [ { name: 'b', type: 'INTEGER' } ]
    , function (error) {
        insertRow();
      }
    );

  function insertRow() {
    db.execute
      ( "INSERT INTO a VALUES (1296333940003)"
      , function (error, rows) {
          if (error) throw error;
          readRow2();
        }
      );
  }

  function readRow1() {
    db.execute
      ( "SELECT b FROM A"
      , function (error, rows) {
          if (error) throw error;
          console.dir(rows);
        }
      );
  }

  function readRow2() {
    db.execute
      ( "SELECT CAST(b AS TEXT) FROM A"
      , function (error, rows) {
          if (error) throw error;
          console.dir(rows);
        }
      );
  }
});


[ { b: '1296333940003' } ]