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
