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