Correct. You have stored integer Unix Epoch timestamps. You cannot do "string" searches on integers (at least not ones like what you have asked for, which involves conversion of an integer representing a Unix Epoch offset to an ISO-8601 string, not to an ordinary "string representation of the integer".
There is no "DATE" type in SQLite3 -- only INTEGER, REAL, TEXT, and BLOB -- and no magical conversion of integers into ISO-8601 strings or v/v. That is: SELECT id, date_type FROM date_types WHERE date_type LIKE '148%'; will work. If you want your date_type integer to be converted to a date string, you need to use the function for converting integer unix epoch offsets into ISO-8601 date strings: SELECT id, date_type FROM date_types WHERE date(date_type, 'unixepoch') LIKE '2018-%-%'; The usual caveats apply for knowing what your timezone is and handling such conversions appropriately for your platform (OS). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of dmp >Sent: Thursday, 5 July, 2018 12:51 >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
