This will however only work in all GMT/UT1/UTC.
If the "input" (ie, the string) is "localtime" then the localtime modifier
needs to be added to the date() function as in:
date(date_type, 'unixepoch', 'localtime') like '2018-%'
Note that you cannot create an index on the expression date(date_types,
'unixepoch', 'localtime') so you might be better off (depending on the size of
the date_types table) converting and using numeric comparisons since the
date_type column itself can be indexed).
select *
from date_types
where date_type >= strftime('%s', '2018-01-01', 'localtime')
and date_type < strftime('%s', '2019-01-01', 'localtime');
---
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: Friday, 6 July, 2018 10:52
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Subject: Re: Date Search
>
>Keith wrote:
>> 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.
>> ~
>> ~
>> 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).
>
>WHERE date(date_type, 'unixepoch') seems the way to go and will now
>focus
>on that as a solution.
>
>danap.
>
>David Raymond wrote:
>> You have to make everything the same type, either numeric or text.
>> ~
>> ~
>
>R Smith:
>> 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.
>> ~
>> ~
>
>Thanks for the repies all. David and R. that would be nice, but the
>context
>here is with a GUI for users so I do not get to define db schema.
>
>I do actually have a test data types table that is loaded with Date
>data
>with both Integer and Text content. Along with also Time, Datetime,
>and Timestamp.
>
>Yes I know all those could be Real or a Blob, but I'm trying to pick
>the
>most likely content that users would store for those types. Integer
>and
>Text seem the most appropriate.
>
>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
