CODEWITHSUNDEEP
datetimesqlite
Nairou
Nairou

Reputation: 3765

Why does SQLite hide the datatype behind a stored date?

SQLite doesn't have a datetime data type, it lets you store datetime values in existing TEXT/REAL/INTEGER fields. In my case, I'm inserting dates into a INTEGER field. Supposedly, SQLite stores dates in an INTEGER field as a Unit timestamp number.

CREATE TABLE 'test' (
    'testDate' INTEGER
);

However, when you insert a date into an INTEGER field, and then read the field back, you don't get an integer (timestamp number) result. You get the date string you just inserted.

sqlite> INSERT INTO test (testDate) VALUES ('now');
sqlite> SELECT * FROM test;
now

If you try to assume that you are getting an integer result, and perform math in the query, you end up with something completely different.

sqlite> SELECT testDate+1 FROM test;
1

To get the underlying type, you have to use date/time functions to convert it to a timestamp (or whatever other format you want).

sqlite> SELECT strftime('%s',testDate) FROM test;
1401293941

Why is SQLite hiding the true data type when you start using dates? What proof can I get that SQLite is internally using the data type I specified when I created the table if it won't actually show me that value?

Upvotes: 1

Views: 246

Answers (1)

luis_js
luis_js

Reputation: 611

If you type 

SELECT *, typeof(testDate) FROM test;

you can see the type that it is being used. In your case, "now" is storaged as TEXT, not as INTEGER.

A value enclosed by single or double quotes is assigned class TEXT.

In sqlite you can declare the column types, or not. sqlite will find out the type. With type declaration, you may influence the variable type, but that's it.

Here is an example (taken from "The Definitive Guide to Sqlite" from Michael Owens)

DROP TABLE IF EXISTS test2;
CREATE TABLE test2(i int, n numeric, t text, b blob);
INSERT INTO test2 VALUES (3.142, 3.142, 3.142, 3.142);
INSERT INTO test2 VALUES ('3.142', '3.142', '3.142', '3.142');
INSERT INTO test2 VALUES (3142, 3142, 3142, 3142);
INSERT INTO test2 VALUES (NULL, NULL, NULL, NULL);
SELECT ROWID, typeof(i), typeof(n), typeof(t), typeof(b) FROM test2;

Give it a try. You'll see that sqlite figure it out, but what you define as column type influence in the outcome.

Upvotes: 2

Related Questions