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-
>] On Behalf Of dmp
>Sent: Friday, 6 July, 2018 10:52
>Subject: [sqlite] Subject: Re: Date Search
>Keith wrote:
>> Correct.
>> You have stored integer Unix Epoch timestamps.  You cannot do
>> searches on integers (at least not ones like what you have asked
>> which involves conversion of an integer representing a Unix Epoch
>> 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
>> you need to use the function for converting integer unix epoch
>> into ISO-8601 date strings:
>> SELECT id, date_type FROM date_types WHERE date(date_type,
>> LIKE '2018-%-%';
>> The usual caveats apply for knowing what your timezone is and
>> such conversions appropriately for your platform (OS).
>WHERE date(date_type, 'unixepoch') seems the way to go and will now
>on that as a solution.
>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-
>> 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
>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
>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
>most likely content that users would store for those types. Integer
>Text seem the most appropriate.
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to