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
