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 

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-
>] On Behalf Of dmp
>Sent: Thursday, 5 July, 2018 12:51
>Subject: [sqlite] Date Search
>SELECT STRFTIME('%s', '2018-01-01');
>SELECT STRFTIME('%s', '2017-01-01');
>CREATE TABLE date_types(
>  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
>value(s) then searching in a numeric range for the given TEXT date?
>SELECT "id", "date_type" FROM "date_types" WHERE "date_type" LIKE
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to