Date Search

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
dmp
Reply | Threaded
Open this post in threaded view
|

Date Search

dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date Search

Keith Medcalf

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-
>[hidden email]] On Behalf Of dmp
>Sent: Thursday, 5 July, 2018 12:51
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date Search

David Raymond
In reply to this post by dmp
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<?)

sqlite> 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:[hidden email]] On Behalf Of dmp
Sent: Thursday, July 05, 2018 2:51 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date Search

R Smith-2
In reply to this post by dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users