Re: [sqlite] Date Search
On 2018/07/05 8:51 PM, dmp wrote: 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? Why not add to the table both the converted Integer date[i.e: strftime('%s', '2017-01-01')] AND the ISO8601 date [i.e: '2017-01-01 00:00:00']? That way you can reference either, use any of them for calculations, and filter/lookup by whichever one suits the occasion best. CREATE TABLE date_types( id INTEGER PRIMARY KEY, INTDate INT, ISODate DATETIME -- This will be NUMERIC affinity. ); INSERT INTO "date_types" ("id", "INTDate", "ISODate") VALUES(1, 1514764800, '2018-01-01 00:00:00'); INSERT INTO "date_types" ("id", "INTDate", "ISODate") VALUES(2, 1483228800, '2017-01-01 00:00:00'); Then it becomes easy to do: SELECT "id", "INTDate", "ISODate" FROM "date_types" WHERE "ISODate" LIKE '2018-%'; and any other date-lookup you can imagine. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Search
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 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
Re: [sqlite] Date Search
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
[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