How to insert Long Type value to SQLite

Question

I have a value from the epoch time like this 1549251913000, I save this value to SQLite. I create the table like the following:

CREATE TABLE TABLE_BOOKMARK (COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, COLUMN_TITLE TEXT, COLUMN_SOURCE TEXT, COLUMN_DATEANDTIME INTEGER, COLUMN_GUID TEXT);

that value is COLUMN_DATEANDTIME but with INTEGER type. but when I take the value, it doesn't match what I expected. it becomes like this -1231280856. Please give me some advice, thanks

I have tried this solution but still mismatch when I get it from SQLite (seems it didn't work with my problem)

`INTEGER` is type for numeric in `Sqlite` . Have you tried after uninstalling app ? — ADM, Feb 04 '19 at 03:48
Okay . have a look at https://stackoverflow.com/a/20676522/4168607. — ADM, Feb 04 '19 at 03:51
there is no problem if i set it with INTEGER type? (when i create table with column dateandtime in Integer type) — Nanda Z, Feb 04 '19 at 03:54

score 4 · Accepted Answer · edited Feb 04 '19 at 04:54

4

Create a column in as INTEGER datatype put LONG value into INTEGER in column

Make sure when you retrieve the value from cursor as LONG

Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
long value = cursor.getLong(0);

For more : SQLite DataType Doc

edited Feb 04 '19 at 04:54

AskNilesh

67,701
16
123
163

answered Feb 04 '19 at 04:00

Dhananjay Waghela

168
1
11

does LONG is exist in SQLite, i thought it only INTEGER – Nanda Z Feb 04 '19 at 04:09
No Only INTEGER exist in SQLite for more information please visit SQLite Docs mention in Answer – Dhananjay Waghela Feb 04 '19 at 04:25

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

Just to clarify the column type is, with one exception, largely irrelevant as any type of data can be stored in any type of column.

The exception is the rowid column or an alias of the rowid column, such a column MUST store an integer. By integer it is a 64bit signed integer and thus encompasses a java long.

The column type itself is also flexible for example CREATE TABLE mytable (mycolumn a_pretty_weird_column_type) is valid (as would be LONG). Such types are converted according to 5 rules to the column affinity.

Putting the above together, using :-

CREATE TABLE IF NOT EXISTS mytable (mycolumn a_pretty_weird_column_type);
DELETE FROM mytable;
INSERT INTO mytable VALUES
  (1549251913000),
    (999999999999999),('Fred'),(x'010203040506070809'),(0.234567),(null),
    ('999999999999999'), -- Note although specified as TEXT as mycolumn is effectively NUMERIC stored as INTEGER
    ('0.234567') -- As above but stored as REAL
;
SELECT 
    *,
    typeof(mycolumn) AS coltype, -- The column type (note as per value not column definition)
    hex(mycolumn) AS as_hex, -- Convert column to a hex representation of the data
    CAST(mycolumn AS TEXT) AS as_text, -- follow rules
    CAST(mycolumn AS INTEGER) AS as_integer,
    CAST(mycolumn AS REAL) AS as_real,
    CAST(mycolumn AS NUMERIC) AS as_numeric,
    CAST(mycolumn AS BLOB) AS as_blob
FROM mytable;

results in :-

The bible as such is Datatypes In SQLite Version 3.

but when I take the value, it doesn't match what I expected. it becomes like this -1231280856

The Issue

As such your issue is nothing to do with the column type, not SQLite as such, rather it's due to using the Cursor getInt method, instead of the getLong method.

One answer says,

Its always better to store date and time in form of Text in sqlite.

This is incorrect, from a space point of view and therefore the underlying efficiency the use of a numeric representation, i.e. 64bit signed integer as per SQL As Understood By SQLite - Date And Time Functions - Time Strings (maximum of 8 bytes), of the date and time will be more efficient than storing the 19 bytes (for an accuracy down to a second).

score 0 · Answer 3 · answered Feb 04 '19 at 03:59

0

As per the docs sqlite does not have default storage class for date and time. Its always better to store date and time in form of Text in sqlite. You can then parse them into Date runtime whenever you want to use them

answered Feb 04 '19 at 03:59

Nikhil Antapurkar

63
8

i will put this in last solution – Nanda Z Feb 04 '19 at 04:09

How to insert Long Type value to SQLite

3 Answers3

The Issue