You have to make everything the same type, either numeric or text. When
possible though you want to do your conversions on your input constant(s), and
not on the stored values. That way you can use an index on the stored value.
sqlite> create index date_type_index on date_types(date_type);
sqlite> explain query plan
...> select * from date_types
...> where strftime('%s', '2018-01-01') <= date_type
...> and date_type < strftime('s', '2018-01-01', '+1 year');
QUERY PLAN
`--SEARCH TABLE date_types USING COVERING INDEX date_type_index (date_type>?
AND date_type<?)
sqlite> explain query plan SELECT "id", "date_type" FROM "date_types" WHERE
"date_type" LIKE '2018-%-%';
QUERY PLAN
`--SCAN TABLE date_types
sqlite> explain query plan SELECT id, date_type FROM date_types WHERE
date(date_type, 'unixepoch') LIKE '2018-%-%';
QUERY PLAN
`--SCAN TABLE date_types
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of dmp
Sent: Thursday, July 05, 2018 2:51 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Date Search
Given:
SELECT STRFTIME('%s', '2018-01-01');
1514764800
SELECT STRFTIME('%s', '2017-01-01');
1483228800
CREATE TABLE date_types(
id INTEGER PRIMARY KEY,
date_type DATE
);
INSERT INTO "date_types" ("id", "date_type") VALUES(1, 1514764800);
INSERT INTO "date_types" ("id", "date_type") VALUES(2, 1483228800);
date_type: Affinity now Integer.
This does not seem to be possible, without some type of conversion to a
Integer(s)
value(s) then searching in a numeric range for the given TEXT date?
SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE '2018-%-%';
danap.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
